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?
VLOOKUP Partial Text Match
 Alist4ir
 Closed
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.



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.

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)

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
just one final question please
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
Could you post your workbook?

Re: VLOOKUP Partial Text Match
Hi
Please see workbook attached as per your request
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?
thank you in advance for your help 

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 

Re: VLOOKUP Partial Text Match
forum.ozgrid.com/index.php?attachment/56069/
Not sure what are you reffering too.
VLOOKUP is not an option here. 
Re: VLOOKUP Partial Text Match
Quote from Robert Mika;682086forum.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
No need to coninue in old threads. Please start your own topic. Thanks.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!