Reference table using cell value in INDEX/MATCH formula

[Dr. Demento]

I'm putting together a master list of phone numbers across the US. In order to keep things neat, each state is on a different sheet. Rather than change the formula 50x, I'm curious how I could adapt this formula so that it reads the cell that indicates the state.

=INDEX(tbl_xx[PHONE],MATCH([@STATE|COUNTY],tbl_xx[STATE|COUNTY],0))

where 'xx' can be read in the [STATE] column of the destination table.

Thanks y'all.

 

[jasonb75]

See if this does what you need, I'm not entirely sure that INDIRECT will work with table references without setting something up to try it.

=INDEX(tbl_xx[PHONE],MATCH([@STATE|COUNTY],INDIRECT("tbl_"&[@STATE]&"[STATE|COUNTY]"),0))

If this doesn't work then I think that you might be out of luck.

 

[Dr. Demento]

Jason,

Thanks so much. It wasn't precisely what I needed, but it was dang close!

Here's my final formula:

=INDEX(INDIRECT("tbl_"&[@STATE]&"[PHONE]"),MATCH([@STATE|COUNTY],INDIRECT("tbl_"&[@STATE]&"[STATE|COUNTY]"),0))

Thanks for furthering my XL education