Finding a value in a table where both row and column are variable

[jungx006]

I'm simply trying to MATCH data on one sheet to data in an array on another sheet.

=MATCH(O6,'2PercentPayTable'!B12:P12, 1) \\ This formula works correctly (except that it gives me an error if I try to use -1, not sure why).

Since the row I'm searching is based on a value and is not always row 12, I created fields in columns q and r to replace "B12" and "P12". The value in q6 is "B12".

=MATCH(O6,"'2PercentPayTable'!"& INDIRECT("q6")&":"& INDIRECT("r6"), 1) \\ returns a #VALUE! error.

When I evaluate it everything seems to come out right except that the last step before the error has quotation marks around the reference to the array.

I've been trying to figure out how to get any kind of formula into the basic expression "sheetname!cell1:cell2", and while there are examples on the forum of using a formula for "sheetname", I can't find any that directly tell me how to replace "cell1" and "cell2" with a formula.

Any idea? Thanks.
Mike

[protonLeah]

request deleted

[Tony Valko]

Try it like this...

=MATCH(O6,INDIRECT("'2PercentPayTable'!"&Q6&":"&R6),1)

[jungx006]

That did it! Thank you Tony!

[Tony Valko]

You're welcome. Thanks for the feedback!