Reference table using cell value in INDEX/MATCH formula

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
548
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.

Code:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
7,471
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Nov 2, 2010
Messages
548
Jason,

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

Here's my final formula:

Code:
=INDEX(INDIRECT("tbl_"&[@STATE]&"[PHONE]"),MATCH([@STATE|COUNTY],INDIRECT("tbl_"&[@STATE]&"[STATE|COUNTY]"),0))
Thanks for furthering my XL education :)
 

Forum statistics

Threads
1,086,259
Messages
5,388,732
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top