Reverse Lookup Function

collegeitdept

Board Regular
Joined
Nov 14, 2008
Messages
185
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.
 
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?
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
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 :(
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top