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....

Rehab Support Worker	Support Worker
Assistant Support Worker	Support Worker
Accommodation Support Worker	Support Worker
Assistant Accountant	Accountant
Management Accountant	Accountant
Deputy Accountant	Accountant

<tbody>
</tbody>

any help is greatly appreciated
Anthony
****** id="cke_pastebin" style="position: absolute; top: 93px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Support Worker

<tbody>
</tbody>

</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