Posts by Alist4ir

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.

  • VLOOKUP Partial Text Match

    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

  • VLOOKUP Partial Text Match

    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?

  • VLOOKUP Partial Text Match

    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

  • VLOOKUP Partial Text Match

    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)

  • VLOOKUP Partial Text Match

    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?