Reverse VLookup?

[djkm]

I thought this was a brilliant title for my problem until I searched the board and found that this question has already been answered - but in reference to a different problem. Ah well.

I have a column, call it E.

I have table, call it Cities. It has a column of, um, cities, but entered in various ways; and a column of regions:

South Cumberland SW
So. Cumberland SW
So. Cumb. SW
Cumberland SW
Galveston S
Rangeley NE
Galv S

If any one of the entries in Column 1 is contained in column E, I want my formula to return the appropriate region code for each row of E.

Note that E8 may or may not be an exact match for the table entry - it may have additional text before and/or after the match text. It may also not appear in the table at all.

I could contruct a whole sequence of "Find" formulae, but I'm thinking there has to be a better way. Also, if I find I need to add another city permutation, it's way easier - & less error-prone - to just extend the table than to create a whole new "Find" calculation column, and then add that column into the calculation that extracts the region.

Thoughts? Ideas? thanks in advance.

Debbie

 

[Aladin Akyurek]

YES!! I am in awe.
It works, PROVIDED that there are no blanks in the Cities range $A$5:$A15.

If there IS a blank, it goes back to returning 0, except when the value to evaluate is itself blank, when it returns #N/A. (I had hoped to name the range, and then if I had to add more cities later, I could do so without having to re-define the range.) I suppose I could try putting the text "BLANK" in the blanks, so it isn't actually blank - hm, let me go try that.

Hm - very interesting. If both Cities & Regions ranges are blank in any row, it returns 0 and #N/A as above. If a row in the Cities range IS blank, but the corresponding Region range has the word "blank", it returns the word "blank" for all rows (again, unless the cell to evaluate is itself empty, in which case it returns #N/A). If the Region range IS blank, but the Cities range has the word "BLANK", it returns results as expected, ie the same as if there were no blanks.

Million-dollar question: WHY does it work?

(I had actually found fuzzy matching in searching the board for ways to solve my problem - I was trying to avoid it because it looked pretty intimidating )

Thanks so much for your help on this, and I'm looking forward to the explanation part!

Debbie

A cell which is empty or blank as lookup value disrupts

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$6,E8),$B$2:$B$6)

This can be prevented by switching to:

=LOOKUP(9.99999999999999E+307,SEARCH(IF($A$2:$A$6="","",$A$2:$A$6),E8),$B$2:$B$6)

which needs to be confirmed with control+shift+enter, not just enter. Such a formula, it must be noted, is quite expensive.

Explanation...

[1]

LOOKUP(9.99999999999999E+307,{1,#N/A,7,#VALUE!,8,0,3,"debbie"})

returns 3, which is the last numeric value in the set. This is due to the fact that LOOKUP invokes a binary search algorithm. See on this:

http://www.mrexcel.com/forum/showthread.php?t=310278

[2]

LOOKUP(9.99999999999999E+307,{#VALUE!,#VALUE!,7,#VALUE!},{"Q","N","S","P"))

will return "S" which is at the same position as the last numeric (7) value is in {#VALUE!,#VALUE!,7,#VALUE!}, the 3rd position.

The formula I posted works like [2].

 

[djkm]

I've inserted placeholders (with reminders not to leave blank, because it disrupts the formula!) in the functionally blank fields, so LOOKUP isn't tripped up.

Thanks for the explanation of binary lookup in the reference post.

I'm sure there's a reference post for the explanation of LOOKUP(9.99999999999999E+307 - but I can't find it. I understand that it's the same as 2^15, ie making it larger than anything that will actually be found, but I'm still not wrapping my head around why. Could you point me to the explanation?

And thanks so much again for your help, I've learned a lot.

Debbie

 

[Aladin Akyurek]

I've inserted placeholders (with reminders not to leave blank, because it disrupts the formula!) in the functionally blank fields, so LOOKUP isn't tripped up.

Thanks for the explanation of binary lookup in the reference post.

I'm sure there's a reference post for the explanation of LOOKUP(9.99999999999999E+307 - but I can't find it. I understand that it's the same as 2^15, ie making it larger than anything that will actually be found, but I'm still not wrapping my head around why. Could you point me to the explanation?

And thanks so much again for your help, I've learned a lot.

Debbie

I thought I had quoted the following link too:

http://www.mrexcel.com/forum/showthread.php?t=102091

I stick to 9.99999999999999E+307, which is mentioned in Excel's Help under:

Excel specifications and limits | Calculation specifications