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.

 

[barry houdini]

Welcome to MrExcel,

Assuming your table starts at A1 and your lookup value (e.g. 435) is in F2 try this formula

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

This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

 

[collegeitdept]

It doesn't work sadly.

It returns "#VALUE!"

 

[barry houdini]

Did you confirm the formula with CTRL+SHIFT+ENTER?

If done correctly the formula will look like this

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

Follow this procedure:

Select cell with formula and press F2 key
Hold down CTRL and SHIFT keys and, at the same time, press ENTER
curly braces should appear.....

 

[collegeitdept]

Thanks it works....with one EXCEPTION...that I hope you can help.

In reality, the table is MUCH larger. If I type in a value not in the table (ie: 123) it always shows up as "CA" (is it because the "MIN" function was used?). How do get back the important "#N/A" error when a value is not in the table?

Thanks.

 

[collegeitdept]

maybe the Match function would work?

But I'm not sure...I have been trying to find a solution for such a long time.

 

[pgc01]

Hi

Using Barry's formula:

=IF(COUNTIF(A2:D5,F2),INDEX(A1:D1,MIN(IF(A2:D5=F2,COLUMN(A2:D5)-COLUMN(A2)+1))),NA())
... confirmed with CTRL+SHIFT+ENTER.

 

[collegeitdept]

But you don't know if the Match function would work, do you?

Thanks so much for your help!

 

[pgc01]

You use Match() to lookup a value in a vector (range with 1 row or 1 column), not a rectangular range m rows * n columns with m and n > 1.

This doesn't mean that you cannot use Match() with such a range, but you must combine the Match() in the formula in such a way that its lookup array is a horizontal or vertical vector at a time. This may result in a more complex formula.

For ex., in your case I believe this is an equivalent formula:

=INDEX(A1:D1,MATCH(TRUE,COUNTIF(OFFSET(A2:A5,0,COLUMN(A1:D1)-COLUMN(A1)),F2)>0,0))
... confirmed with CTRL+SHIFT+ENTER.

Notice that the #N/A error in this case is generated directly by the match. In the previous formula, the error was forced using an external test.

 

[crimson_b1ade]

You could also try a custom vba function (see below as example which you would paste into a module within the vbe project window - hold down Alt+F11 keys, on Tool Bar Click Insert\Module). You use like a regular Excel Function The first argument is the value you want to lookup, the second argument is the range (select the entire table) (ex: =myLookup(G6, A1:D5):

Function myLookup(myValue, myRange As Range)
Dim c As Range
For Each c In myRange
If c.Value = myValue Then

myLookup = Cells(1, c.Column).Value
Exit Function

End If
Next
If myLookup = 0 Then myLookup = "#N/A"

End Function



Confucius say to become master fisherman, one must first become master baiter.