Reference table using cell value in INDEX/MATCH formula

Dr. Demento

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

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,675
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
552
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,100,142
Messages
5,472,754
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top