Reverse Lookup Function

[collegeitdept]

I have been trying to figure out how to solve the following question:

Data:


CA AZ WA OR
452 2302 1251 236
3176 228 491 219
1552 435 451 210
3024 244 1751

The columns are States (regions), and the rows contain store location numbers in their respective region.

I have been trying to find a solution that would look-up in the range a store number/value (ie: 435) and return the the Region it is located in (ie: "AZ").


Does any one know how to solve this problem?


Thanks.

 

[collegeitdept]

Yes this formula works when the data table workbook file is open.

=OFFSET(INDEX('[Store List.xls]Sheet1'!$H$2:$H$972,MATCH($A3,'[Store List.xls]Sheet1'!$A$2:$A$972,0)),1,0)


I would really like this to be fixed. It's looking at the same data table...


Do you know of another formula I could use/how to fix it?

 

[barry houdini]

OFFSET also doesn't work with closed workbooks. If you are just offsetting one row down then you don't really need OFFSET just move the index range down by one, i.e.

=INDEX('[Store List.xls]Sheet1'!$H$3:$H$973,MATCH($A3,'[Store List.xls]Sheet1'!$A$2:$A$972,0))

 

[collegeitdept]

I can't.... there are SOO many rows (ie Centers).


Basically the Center finds and matches a center in the table... then gets the corresponding value in the cell below & to the right of it.

Each center has data on multiple rows (3 rows). Vlookup cannot work because it only matches the lookup value to the first instance row.

do you know of another way?

 

[collegeitdept]

Could an Indirect work?


If so...how would that formula work?

 

[barry houdini]

If your OFFSET formula worked then the formula I posted with just INDEX and MATCH should do the same. It can be copied down a column just like the OFFSET version, you don't have to change it for every row.

INDIRECT isn't an option, that doesn't work with closed workbooks either