convert text string to cell address to use in a nested function

**scrid** · 10-12-2012, 10:01 PM

Hi all,

I'm creating an automated spreadsheet to use in my football pool and am running into some trouble. I have 2 address functions "ADDRESS(MATCH(4,$A$1:$A$999,0)+2,3)" and "ADDRESS(MATCH(4,$A$1:$A$999,0)+40,3)" which return the text strings $C$132 and $C$170, respectively. I would like to nest these results in a "MATCH($F$7,$C$132:$C$170,0)" however the address function returns a text string and not an actual cell location. Essentially I want to make this function work:
"MATCH($F$7,(ADDRESS(MATCH(4,$A$1:$A$999,0)+2,3))

ADDRESS(MATCH(4,$A$1:$A$999,0)+40,3),0)"
I've tried the indirect function but this returns the $C$132 and $C$170 values not the locations

Thanks,
Paul

**JBeaucaire** · 10-13-2012, 03:08 AM

This should work with the INDIRECT() function.

=MATCH($F$7, INDIRECT(ADDRESS(MATCH(4,$A$1:$A$999,0)+2,3)&":"&ADDRESS(MATCH(4,$A$1:$A$999,0)+40,3)),0)

You could also use the OFFSET() function:

=MATCH($F$7, OFFSET(INDEX($C$1:$C$999, MATCH(4,$A$1:$A$999,0)+2),,,38,), 0)