# VLOOKUP Partial Text Match

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• Hi, I have downloaded my bank statement & I want to categorise my expenses, instead of doing this manually I want to use Index & Match to return a category in a next column which I can then filter, however I don’t have an exact match!
i.e. in the description column I get “Vodafone 275” & “Vodafone 135” so﻿ I just want it to match the 1st 4 or 5 characters & ignore the rest,
I have created 2 column Description & Category so when I find Vodafone or BT, I get Telecom in the next column so I can filter, can you please help?

• Re: VLOOKUP Partial Text Match

Could you confirm whether you wanted to HIRE help, or were you looking to get FREE help. It's HIRE help, then you should post an amount willing to pay, and also pay 10% to [email protected]

Otherwise, if you intended for the free help, I can move your post to the HELP section. Please confirm either way.

Cheers,

S M C

• Re: VLOOKUP Partial Text Match

Hi, I'm sorry I'm new at this, this is a one off & I was hoping to get a free help please

• Re: VLOOKUP Partial Text Match

Alist4ir, to do a Vlookup for the initial 4/5 letters, you can use =VLOOKUP(LEFT(A1,5),LookupArray,2,0)

Cheers,

S M C

• Re: VLOOKUP Partial Text Match

Thank you so much for your quick response
however I have followed your instruction & used your formula below on a sample data & entered it in cell B30, with A30 is the description which I want to Match & Categorise but get #N/A in B30, any idea what I'm doing wrong?
=VLOOKUP(LEFT(A30,5),\$A\$1:\$B\$8,2,0)

• Re: VLOOKUP Partial Text Match

Looking at the topic:
LEFT(A30,5) gives you "Vodaf", and this is being looked at column A in the lookup table.
But becouse you do not have any "Vodaf" but Vodafon the formula can not find any match and is returning #N/A
Try
=VLOOKUP(LEFT(A30,5)&"*",\$A\$1:\$B\$8,2,0)
If still does not work attached your wrokbook

• Re: VLOOKUP Partial Text Match

you are the MAN!!!
worked like a charm
many many thx

• Re: VLOOKUP Partial Text Match

I ran the formula & it generally works however I still got some error due to data not being clean in the description column
say I have "Vodafone 2gb34fc" & "Vodafone bill 2gb34fc"
if I wanted not to restrict the search on only the 1st 5 characters etc but to lookup a ref. anywhere in the string such as "2gb34fc" how would I rewrite the formula please?
pls note "2gb34fc" will be in column 1 of the LookupArray

• Re: VLOOKUP Partial Text Match

• Re: VLOOKUP Partial Text Match

Hi
As you can see the Ref data is not always in the same place in the string there could be leading spaces etc. I think I have to utilise "*"&"*" somehow but not sure how?

## Files

• Re: VLOOKUP Partial Text Match

=lookup(9.99999999e+307,search(\$a\$2:\$a\$5,a10),\$b\$2:\$b\$5)

• Re: VLOOKUP Partial Text Match

Hi
any chance of getting a response to my question above please, I really like to finalise this?
Thanks

• Re: VLOOKUP Partial Text Match

ops sorry I didnt see the 2nd page but now that I've seen it, it doesnt make much sense
=lookup(9.99999999e+307,search(\$a\$2:\$a\$5,a10),\$b\$2:\$b\$5)
how does
9.99999999e+307 related to my data?

• Re: VLOOKUP Partial Text Match

=INDEX(\$B\$2:\$B\$5,MATCH(TRUE,ISNUMBER(SEARCH(\$A\$2:\$A\$5,A10)),0))
Confirm COntrol+Shift+Enter

• Re: VLOOKUP Partial Text Match

Hi,
Thanks for the formula but like VLOOKUP which I previuosly used its looking for an exact match
however as mentioned I still get some error due to data not being clean in the description column
if you refer to the spreadsheet (I've attached previously & will try to attach again), I have "TR4444" in the array which I want to match - in the data column I could have "DD Vodafone Ref. TR4444" or "Ref. TR4444" so its not an exact match but has "TR4444" common - I need it to look for the "TR4444" anywhere in the string
I dont have a prefernce to use Index or Vlookup
thank you

## Files

• Re: VLOOKUP Partial Text Match

Quote from Robert Mika;682086

forum.ozgrid.com/index.php?attachment/56069/
Not sure what are you reffering too.
VLOOKUP is not an option here.

Hi Robert, Can you please explain what is this part for? MATCH(TRUE;ISNUMBER...

Why do I need this "TRUE;ISNUMBER" inside the MATCH function?

• Re: VLOOKUP Partial Text Match

vcaldo

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!