Index and match

[jollyfella]

Hi ,

I am learning to use the index and match worksheet functions.

I read through some examples and tried to set up a spreadsheet as attached.

For some reason, it is working only partially. For some cells the value is returning correctly and in some it is returing #ref.

Can somebody let me know what is it that I am doing wrong? I am not sure where my understanding went wrong.

Thanks n advance for your help,

jf

[DonkeyOte]

Welcome to the Board.

INDEX when range is Matrix works along the lines of:

Please Login or Register  to view this content.

In your formula you have the row / column formulae transposed, ie you're calculating:

Please Login or Register  to view this content.

Given you have more rows than columns you end up referencing cells outside of your matrix, eg:

Please Login or Register  to view this content.

this is referencing 1st row and 4th column of range F2:G15, this will generate a #REF error given your range has only 2 columns (there is no column 4).

Your formula then should read:

Please Login or Register  to view this content.

[jollyfella]

thanks for your welcome message, Donkey ote.

and thanks for the quick fix. it did work perfectly this time around.

jf