# Reference sheet name from cell value in match formula

I have one sheet with:

• column A filled with dates in ascending order
• row 1 filled with currency tickers (USD, JPY, EUR, AUD, CHF)

several other sheets each named exactly as the currency tickers
each sheet as column A filled with dates in ascending order, column B with the currency daily average

I am trying to fill cells in my first sheet with values from the other sheet based on the row’s date in column A and the column’s ticker name in row 1 as the sheet name to look at.

assuming I am in ROW 547 and COLUMN G , I am using this formula in my first sheet:

but I am stuck with the MATCH formula:

this works:
=MATCH(A547,USD.\$A\$1:\$A\$1000,-1)

this doesn’t:
=MATCH(A547,G1&"."&\$A\$1:\$A\$1000,-1)
as it appears like the range \$A\$1:\$A\$1000 is in the same sheet where the formula is and not in the one named in the cell G! as I am hoping.

any help is appreciated…

It should be like this.

``````=INDIRECT(G\$1&".B"&MATCH(\$A547;INDIRECT(G\$1&".\$A\$1:\$A\$1000");1))
``````
• You correctly used `INDIRECT` for the outside part but forgot to use it on the inner part.
• `G1 & "." & \$A\$1:\$A\$1000` is not correct as it attempts to concatenate an actual range. The range should be a string, that is, `G\$1 & ".\$A\$1:\$A\$1000"`.
• If the dates are in ascending order, then type for MATCH should be 1. If the function works with -1, then perhaps your dates are in descending order.
• More `\$` signs were added for easier filling.

you are correct, I should have used INDIRECT also in the inner part. I could make it work, thank you so much for the help!