Search code examples
functiongoogle-sheetsmatchgoogle-sheets-formulanotation

MATCH function - How to use a string as a range parameter

I need to create a generic formula to get a value from a MATCH command but I don't know exactly where the range of data is. I can not name the range.

The MATCH function does not work with a string as a range parameter.

Final result like this:

=MATCH("Risks";'SheetName'!B:B;0)

Using the code below.. considering A1 contents =SheetName

=MATCH("Risks"; A1&"!:B:B";0)   

=MATCH("Risks"; Concatenate(A1;"!:B:B");0)

But doesn't work

Has anyone ever needed to use the MATCH command this way? Any workarounds?

Solution

  • INDIRECT will help you get the job done.

    The following supposes that you're specifying the sheet name in cell A1 and that the range you want to search is in column B of the specified sheet.

    =MATCH("Risks",INDIRECT(A1&"!B:B"),0)