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]

Try whether the following helps...

=LOOKUP(9.99999999999999E+307,SEARCH(Cities,E8),Regions)

 

[djkm]

Try whether the following helps...

=LOOKUP(9.99999999999999E+307,SEARCH(Cities,E8),Regions)

This returns mostly 0, except a few #N/A where column 8 is blank, and one #N/A inexplicably in the top row (row 8), for a city that in row 103 also returns 0.

And my curiosity is alight - what is the 9.99~E+307 supposed to do?

Thanks, still anxious for help....

D.

 

[Aladin Akyurek]

This returns mostly 0, except a few #N/A where column 8 is blank, and one #N/A inexplicably in the top row (row 8), for a city that in row 103 also returns 0.

And my curiosity is alight - what is the 9.99~E+307 supposed to do?

Thanks, still anxious for help....

D.

We can come back to that big number later.

Would you care to provide also a sample of values you have in column E?

 

[djkm]

2008burials.xls
	E	F	G	H
15	Scarborough,ME		#VALUE!	ME
16			#VALUE!	UNK
17	Portland		ME
18	Portland,athome		ME
19	MercyHos.Portland		ME
20	Portland,ME		ME
21	Portland		ME
22	OldOrchardBeach,ME		#VALUE!	ME
23	GosnellHouse-Scarborough,ME		#VALUE!	ME
24	Portland		ME
25	Portland,ME		ME
burials

In a different part of the sheet, I entered your formula as:

=LOOKUP(9.99999999999999E+307,SEARCH('2008burials.xls'!GPtowns,E8),'2008burials.xls'!GPtowns)

This is actually returning all #N/A. I got the previous result (some #N/A and mostly 0) when instead of pointing to the name of the table, I pointed to the actual range of cells covered by the table - GP list!A6:A14 for the towns, and GP list!B6:B14 for the regions. It occurred to me that wasn't what you intended, so I changed it - but maybe this wasn't what you intended either.

My current idea is to try to standardize the town names first, then do the lookup, but I run into the same problem, namely automatically identifying that any one of several town names or parts of town names appears anywhere in that location string, as shown above.

Thanks for being interested enough to try

Debbie

 

[Aladin Akyurek]

Could you post just...

1) 5 rows from Cities;
2) 5 rows from the corresponding Regions; and
3) 5 rows from column E?

You can post them successively, as shown in the examole below...

Sample-1
A
B
F
D
K

Sample-2
2
1
7
7
4

Sample-3

AB
BD
XF
GH
MN

 

[djkm]

OK, to avoid confusing myself, the table is actually named GPtowns.

5 Cities from this table:

<table x:str="" style="border-collapse: collapse; width: 48pt;" width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">So. Port.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">So. Portland</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Scarb.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Scarborough</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Portland</td> </tr> </tbody></table>
Regions: at the moment, there's only 1 region, so all the cells are GP:

<table x:str="" style="border-collapse: collapse; width: 76pt;" width="101" border="0" cellpadding="0" cellspacing="0"><col style="width: 76pt;" width="101"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 76pt;" width="101" height="17">GP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">GP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">GP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">GP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">GP</td> </tr> </tbody></table>
5 rows from Column E:

<table x:str="" style="border-collapse: collapse; width: 460px; height: 256px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 65pt;" width="87"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt; width: 65pt;" width="87" align="left" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17"> <table x:str="" style="border-collapse: collapse; width: 161pt;" width="214" border="0" cellpadding="0" cellspacing="0"><col style="width: 161pt;" width="214"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt; width: 161pt;" width="214" align="left" height="17">Portland, at home</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">Mercy Hos. Portland</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">Portland, ME</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">Portland</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">Gosnell House - Scarborough, ME</td> </tr> </tbody></table></td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl19" style="height: 12.75pt;" align="left" height="17">
</td> </tr> </tbody></table>

 

[Aladin Akyurek]

OK, to avoid confusing myself, the table is actually named GPtowns.

5 Cities from this table:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str=""><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>So. Port.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>So. Portland</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Scarb.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Scarborough</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Portland</TD></TR></TBODY></TABLE>
Regions: at the moment, there's only 1 region, so all the cells are GP:

<TABLE style="WIDTH: 76pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=101 border=0 x:str=""><COLGROUP><COL style="WIDTH: 76pt" width=101><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 76pt; HEIGHT: 12.75pt" width=101 height=17>GP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>GP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>GP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>GP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>GP</TD></TR></TBODY></TABLE>
5 rows from Column E:

<TABLE style="WIDTH: 460px; BORDER-COLLAPSE: collapse; HEIGHT: 256px" cellSpacing=0 cellPadding=0 border=0 x:str=""><COLGROUP><COL style="WIDTH: 65pt" width=87><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="WIDTH: 65pt; HEIGHT: 12.75pt" align=left width=87 height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17><TABLE style="WIDTH: 161pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=214 border=0 x:str=""><COLGROUP><COL style="WIDTH: 161pt" width=214><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="WIDTH: 161pt; HEIGHT: 12.75pt" align=left width=214 height=17>Portland, at home</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>Mercy Hos. Portland</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>Portland, ME</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>Portland</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>Gosnell House - Scarborough, ME</TD></TR></TBODY></TABLE></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl19 style="HEIGHT: 12.75pt" align=left height=17>

</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 339pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=451 border=0 x:str><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797183 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 80pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=107 height=17>So. Port.</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=40>DF</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 21pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=28> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=29> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 158pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=210> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 28pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=37> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>So. Portland</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">MH</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Scarb.</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">JN</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Scarborough</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DX</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Portland</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ZQ</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Portland, at home</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=LOOKUP(9.99999999999999E+307,SEARCH($A$1:$A$5,E7),$B$1:$B$5)">ZQ</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Mercy Hos. Portland</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=LOOKUP(9.99999999999999E+307,SEARCH($A$1:$A$5,E8),$B$1:$B$5)">ZQ</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Portland, ME</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=LOOKUP(9.99999999999999E+307,SEARCH($A$1:$A$5,E9),$B$1:$B$5)">ZQ</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Portland</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=LOOKUP(9.99999999999999E+307,SEARCH($A$1:$A$5,E10),$B$1:$B$5)">ZQ</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Gosnell House - Scarborough, ME</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=LOOKUP(9.99999999999999E+307,SEARCH($A$1:$A$5,E11),$B$1:$B$5)">DX</TD></TR></TBODY></TABLE>

Cities/Regions data is A2:B6.

Values to evaluate in E8:E12.

Note that I changed the values B2:B6 in order to better show what the formula is doing...

F8, copied down:

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

Does this yield the results that you expect?

If not, you probably need fuzzy matching. Do a search on this term at this board.

 

[djkm]

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

 

[djkm]

Oh - forgot to say I'm especially impressed that it doesn't return the region code for Portland when the city is South Portland.