[SOLVED] Getting Match to use a cell reference as part of another cell reference

Any time you use the "&" to join strings, Excel automatically returns this as text - not a range address. You must use the INDIRECT function to convert the text string into a range address.

Try this:

=INDEX($S$5:$S$41,MATCH(H5,INDIRECT(N3&"$5:"&N3&"$41"),FALSE),1)

I'm pretty sure you'll need INDIRECT() for that syntax. But maybe you just need an INDEX/MATCH/MATCH approach.

If you post a sample workbook so we can see the actual data structure and what values you're trying to match to the table, maybe we can suggest something less volatile than INDIRECT().

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Yeah, INDIRECT() is great, I use it in small doses. If you find you are needing a LOT of these formulas, you might want to come back and see if we can help find a less volatile approach, INDIRECT() formulas are calculating 100% of the time, every time you make any change anywhere in your workbook. Most other functions don't do that. So, too many INDIRECT() can cause performance issues, but I'm sure you'll notice if that starts to happen.

Go team.

Glad you got the solution you needed.

Thanks for marking the thread as solved and adding to reputations.