Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vlookup & Index Match

0

    I have Excel workbook with two worksheets; 'Input Data & By Month' worksheets using dates and numbers, I've been trying to combine 'vlookup & match together so I can find the Input Data by each individual month. Couldn't get this combination to work, so I used 'Index Match'. vlookup & Index Match they do work individually, but, when I did combine the two 'vlookup & Index match' together, I got an "REF!" error, not sure if this is the wright formula function to choose . Can I get some assistance and help , 'Please' with this excel formula function?

Many Thanks

Answer
Discuss

Answers

0
Selected Answer

It's never a good idea to use a formula that doesn't work as an explanation for what you want. In this case the outcome is that I have no idea of what you wish to achieve. I made a guess based on your form and came up with this formula.

=IF(MONTH($C3)=(COLUMN()-COLUMN($D3)), VLOOKUP($B3,'Input Data'!$B$3:$F$5000,5,FALSE), "")

Paste it to 'By Month'!E3 and copy it from there to all other cells in columns E:P. Increase or decrease the range I extended to F5000. Note that the VLOOKUP function seems superfluous here because column B has the row number the lookup seeks with its help. Therefore INDEX('Input Data'!$B$3:$F$5000,$B3,6) would be equivalent. This is also true for your formula in 'ByMonth'!R3.

Your formula in 'ByMonth'!S3 is a fluke. It determines that the date in C4 is the 2nd date in 'Input Data'!$E$3:$E$49 and concludes from this that the result you want must be February. This is pure coincidence. I recommend this formula for column S instead.

=INDEX($E$2:$P$2,(ROW()-2))

ROW() returns the number of the row in which the formula resides, 3 if it's in S3. You want 1, to point at the first cell in $E$2:$P$2 = "January". Therefore it's (ROW()-2), and this formula counts from 1 and up as you copy it down.

It's not clear what you want in 'By Month'!T:U. But if you want a monthly total you might get it with SUMIF or SUMIFS directly from the Input Data sheet by either setting a month as criterium or two dates, like >= January 1 AND < February 1. However, since you already have the monthly data on the By Month tab (brought there by the first formula in this post) the formula below should be easier to construct.

=SUM(INDEX($E3:$P$5000,,(ROW()-2)))

Paste to T3, adjust the last row in the range, and copy down till December.

Discuss

Discussion

Thank You Kindly, Variattus for the explanation on this issue, it worked quite well, quite a different formula function outcome than I anticipated, show to me how powerful Excel is.
jdgrapes (rep: 14) Jan 21, '20 at 1:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login