Resolved: Doubt regarding Match and Index combined.
Please see in the formula bar that I fixed rows for index and left columns dynamically(A$2:E$7), but excel is asking me to add column number too (blue arrow in the picture) otherwise it is showing " #REF! ". I don't understand why I should add the column number as it doesn't affect my final answer even though I copy the same formula in different cells without changing the column number.
Hello,
Good to hear from you.
In this case, you have selected the array from A2 to E7. This is a rectangular shape, which contains rows and columns. The INDEX function requires you to specify a row number (which you obtain with MATCH), but also a column number (for which you need a second MATCH function in this case). If you don't specify the column number, Excel wouldn't know which cell should be selected from the respective row specified with the first MATCH function.
Hope this makes sense!
Best,
Ned
Thanks for replying, I understand that we should give column number in the formula but in the video you didn't too gave any column number in your example and it worked in the video. So I guess I am missing something. Please explain.
Please pay attention that in the video, as the first argument of the INDEX function i've selected only one column. So, MATCH doesn't need a column number- this is only 1 column. In your case, this isn't valid. You've selected multiple columns in the first argument of INDEX.
Got it, my bad. Thank You.
In the video Ned you drag with your cursor the Function and it applies in all other cells/columns. in my version though it doesnt seem to work with such a broad spectrum. could it be a version limitation?