Vlookup question

[stanleyng]

Hello Mr Excel experts,

I am not sure how to explain what I need in words so I have attached an image for illustration, hopefully that can help me explain better.
Basically I want to lookup cell F4:F8 against the table range B3:C8 and return the Tier number as the Result. (for e.g. any HSBC branches should return " 1 " as the result)
The problem is I cannot find any formula that can help me do that so now I am using a very time consuming way and manually removing the branch from the bank name in a new cell and do a simple vlookup.

Hope I can find a feasible solution here, thanks in advance!

 

[SanjayGMusafir]

Try this -

=XLOOKUP(LEFT(F4,(FIND(" ",F4,1)-1))&"*",B4:B8,C4:C8,,2,1)

 

[stanleyng]

thanks man! it works for me

 

[SanjayGMusafir]

Glad to help you. Thanks for the feedback