Why doesn't the Index+Match example in this lesson need a column number for the Index function?
I thought I finally understood Index+Match after trying very hard in the Intro to Excel course but I guess not haha! The Index+Match part of the exercise is very challenging. I tried following along with the lesson but I kept getting N/A and REF errors even when I came up with the formulas myself. So then I looked at the solution and it had a column number for the Index function. I don't understand why sometimes you need a column number and other times you don't. I also don't understand the solution formula but I think I just need to look at it more.
Hi Dylan,
Good to hear from you! I'm glad you're committed to learning here!
The idea is that INDEX gives you the results. And MATCH tells Excel where is the result. The INDEX function is an array of cells, and then we use MATCH to find a lookup value and provide a specific result within that array.
Best,
Ned
I do understand how they work alone but not when you put them together. How did MATCH find the correct cell in the video without the INDEX column number? =INDEX(C$4:C$12;MATCH($I4;$B$4:$B$12,0)). This formula doesn't need a column number but when I don't use one in the exercise I get errors and I don't know why. I tried making July the lookup value and I tried making the store the lookup value but I still get errors.
I think I figured it out. Based on the Exercise Solution, when you want to use the Index + Match functions to fill out rows you have to put the MATCH function in the row argument of the INDEX function (and add a column number). When you want to use the Index + Match functions to fill out a column you have to put the MATCH function in the column argument of the INDEX function (and add a row number).
I was just confused because the formula in the video lesson didn't have a column number argument in the INDEX function but rows and columns were successfully filled.