Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function

Hello excel-experts,


The example excel-file has two sheets, named "Source" and "Retrieve".
In "Source" there is data that I want to look up in sheet "Retrieve" using the functions "index" and "match".
Because the data is on a different sheet, I could use a function like "= index( Source!$A:$C , 10 , 3)".
Later I will change that "10"-value with a match-function, but that's besides the point right now.

I want some flexibility. On the sheet "Retrieve" cell B1 has the text "Source" (so the name of the data sheet), cell B2 has the text "A" (so the first column in the range) and cell B3 has the text "C" (so the last column in the range).

Instead of using the function "= index( Source!$A:$C , 10 , 3)" I want to use the texts in the cells B1, B2 and B3, so that my function still works if I decide to change the sheet-name or the column in the ranges.

Is there a way to do this without declaring a range-name to the range Source!A:C ???


I would really appreciate any help on this.


Kind regards,
Fie Buls


P.S. Please let me know if you can help but my question or excel-sheet is not clear enough. I am Dutch, so I hope that downloading my excel-file translates the formulas automatically.

Hi Ben van Jonhson,

I really appreciate your help and am happy I got a response so quickly.
I tried your formulas and in cells C1 I see the promising text-string 'Source'!$A:$C and in D1 I see 'Source'!$A:$A.

In cell D5 you do not use your formulas in cells C1 and D1, but that is the whole idea I presume.
However I get an "#N/B" error if I use in cell D5 the formula:

=IF($B5="","",INDEX( C1 ,MATCH($B5, D1 ,0),3)) where C1 and D1 are the strings mentioned above.


In the attached file you can see the result and the #N/B.

Am I making a small, easily repairable, mistake?


Kind regards,
Fie Buls

Hi Ben,

I found out that if I use your formulas in C1 en D1 and then add the INDIRECT-function, it all works perfectly.
Thanks for the initial help.


Kind regards,
Fie Buls