lookup with wildcard

[p4nny]

Hi,

Assuming these are 2 seperate lists in different tabs

I would like to return the value in the second column as it is the closest match.

Is this possible?

If I wanted to return the first column i would use vlookup("*"&a1....

[TABLE="width: 500"]
<tbody>[TR]
[TD]Rehab Support Worker[/TD]
[TD]Support Worker[/TD]
[/TR]
[TR]
[TD]Assistant Support Worker[/TD]
[TD]Support Worker[/TD]
[/TR]
[TR]
[TD]Accommodation Support Worker[/TD]
[TD]Support Worker[/TD]
[/TR]
[TR]
[TD]Assistant Accountant[/TD]
[TD]Accountant[/TD]
[/TR]
[TR]
[TD]Management Accountant[/TD]
[TD]Accountant[/TD]
[/TR]
[TR]
[TD]Deputy Accountant[/TD]
[TD]Accountant[/TD]
[/TR]
</tbody>[/TABLE]

any help is greatly appreciated
Anthony
****** id="cke_pastebin" style="position: absolute; top: 93px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]Support Worker[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[steve the fish]

Heres a way based on what you have there:

=INDEX(workers,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(workers,A1)),0),0))

where workers is a named range with in this instance Support Worker and Accountant in it.

 

[p4nny]

Works great thanks. The only thing is my dataset is 7000 rows and it runs quite slow but I can live with it.

Thank you

 

[p4nny]

Works perfect - the reason why it was slow was because I'd highlighted the whole column in the named range.

Thanks again