Partial Lookup

LJM said:

I've been hunting around for a solution for sometime now and stumbled across this forum.

Just signed up to say thank you and I shall be using this website some more when I need a solution!

Liam

Click to expand...

Hi, just joined this forum too.

I have a similar issue and would very much appreciate your help:

I have a list of computer host names, part of which indicates whether it's a server, client, till or laptop etc (col B). The list is extacted from another tool and it's a few thousands rows - I have removed other columns as they are not important for this exercise but would be retained in the final version.

In the lookup tab I have the partial string (col A) and what they should match up to (col B, return value). For example, if the hostname contains "cl", it's a client machine, if it contains "srv" it's a server.

I have tried adapting a previously successful vlookup (that was matching the whole exact field) with the asterisks but had no joy, for some reason it keeps returning the last value in the list or at worst it gives an error. I also tried a few of the combinations given in this thread with no luck.

The search string could be anywhere in the hostname, in the middle or at the end. Sample data attached. The lookup list could expand to include other abbreviations. IF a match is not found, then n/a or any recognisable string should be added instead.

The values returned (type of machine) would then be used to create pivot tables of type of machine and whether it's online or offline (no problem with that). As this needs to be done on a daily basis at the moment, and we are talking thousands of machines, any sort of manual matching is impossible.

I am a newby at vlookup (also not a programmer) so please go easy on me

Thanks in advance for your help.

View attachment gurutest.xlsx

Try this array formula

=INDEX(lookup!$B$1:$B$8,MATCH(TRUE,ISNUMBER(SEARCH(lookup!$A$1:$A$9,devices!B2)),0))

Maybe something like this? Pls see the file

OMG Azumi, that's fantastic, thank you very much for the prompt response! It works a treat, down to spotting my own typos (there was a hostname ending in svr which should have been picked up as server, but was marked as not recognised - first port of call: the typo in lookup a2 which should have read svr not srv).

What does the 2^15 do? I am trying to undestand how it's been formulated (my formula skills are very basic - trying to learn).

Bob

I tried that formula but I get the same result as with mine : n/a. Having said that I have only just tried (off to a meeting now) so I haven't been able to analyze it or see if I did something wrong. The Index/Match function is something I have seen mentioned before so quite interested to learn how it works too.

Guys thanks again for your help!