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]

pgc01,
that was the reason why I wanted to use the Match function. I need the N/A error.

Thank you so much for all your help.

crimson_b1ade,
I stay away from VBA.

I worry about compatibility across different versions of Excel.

Also, I just switched to the Mac and now I'm using Excel 2008 and also Excel 2007 for Windows at work.

But once again, thanks for your help.

 

[patbarb]

You may be asking this question because you have no other options for data format, but if you have a list somewhere of all the stores and their respective state locations you could just make a separate lookup table (store #'s in 1st col., states in 2nd) and do a VLookup in your main worksheet for each store #. Also, if you chose the VLookup 'exact match' option, you'd get the #N/A that you want when a new store # is typed in.

 

[xenou]

When you say your table is much larger, how large?

If I may state the obvious, this is not a good way to store your data. Much easier would be a simple column of data:

1 CA
2 WA
3 OR
4 AZ
5 CA
6 WA
7 NM

Etc. This would also be normalized for use in databases and other data systems - and of course would be a snap for Excel.

 

[collegeitdept]

Alexander Barnes,

How would this work?

Could you please provide me an example, with the data table and a working formula?


Thanks so much.

 

[barry houdini]

As patbarb also pointed out, it would be easier with a lookup table, e.g. list all the store numbers in A2:A100 or however far you need, with corresponding State abbreviation in B2:B100 then, with a specific store number in D2 you can use this formula

=VLOOKUP(D2,$A$2:$B$100,2,0)

 

[collegeitdept]

pgc01,

PLEASE HELP!


I used the your formula:

=IF(COUNTIF(A2:D5,F2),INDEX(A1:D1,MIN(IF(A2:D5=F2,COLUMN(A2:D5)-COLUMN(A2)+1))),NA())

only I modified it so that it looks in a separate Excel file for the data table.... It works fine.

HOWEVER, this formula requires the separate Excel file containing the data table to be open at all times. If I close that file, all the values return as "#VALUE!".

PLEASE HELP!!!

 

[collegeitdept]

I believe it may be because I i had to do CNTRL+SHIFT+ENTER


actually whenever formula's contain this, for some reason Excel returns the "#Value!" error when the document is closed.


How do I fix this?

 

[xenou]

CountIf will not work with a closed external reference.

Maybe,
=IF(SUMPRODUCT(--(A2:D5=F2)),INDEX(A1:D1,MIN(IF(A2:D5=F2,COLUMN(A2:D5)-COLUMN(A2)+1))),NA())

 

[collegeitdept]

Alexander Barnes,

It worked perfectly!

It was because of the "countif" function.... But i don't understand why.


However, the error also happens (error "#VALUE!" when source is closed) when i use the Offset function with Index/Match function.


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


But as you can see, a "countif" function isn't utilized. How can I fix that?

Thanks.

 

[xenou]

It's a known limitation of sumif, countif, and countblank. Now you know it too!

Does your offset/index/etc formula work when the other workbook is open? Perhaps the problem is of another kind with that one?

Alex