Formula to find closest match

[p4nny]

Hi,

I would like a "lookup" formula that would return the best possible match. (closest match)

Example

lookup_value - "BUILDING 421" ???

Array

column a column b
BUILDING 451 Manchester
BUILDING 361 Manchester
BUILDING 201 Manchester
BUILDING 108 Manchester
Office 12 Liverpool
Office 328 Liverpool
Office 214 Liverpool

I would like the lookup to return "Manchester" in this case.

Hope this is clear.

Thanks

 

[gaz_chops]

What would you like for

[TABLE="width: 141"]
<tbody>[TR]
[TD]BUILDING 451[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]BUILDING 411[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]BUILDING 201[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]BUILDING 108[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]Office 12[/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD]Office 328[/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD]Office 214[/TD]
[TD]Liverpool[/TD]
[/TR]
</tbody>[/TABLE]