Forum Discussion

Robert Cox's avatar
Robert Cox
Copper Contributor
Sep 22, 2018

Vlookup +

I would like to keep track of my finances and have downloaded my bank statement to an excel workbook the sheet is called accounts

I have also produced a sheet (codes) where I have found the common string within each statement entry and trimmed it to that 

IE accounts would show

CodeDateDescription
 03/01/2018CARD PAYMENT TO ASDA SUPERSTORE,71.37 GBP, RATE 1.00/GBP ON 01-01-2018

 and codes would show

Descriptioncode
CARD PAYMENT TO ASDA SUPERSTORE31

I then give each unique entry a code number

I would like excel to lookup the first entry ( Asda) in accounts and find the lookup that exactly matches

this in codes and then add the code number ie 31 to accounts/code

I see the text being something like V lookup (the first entry in description ( say c2), match it exactly with codes ( in this case Description (say a2) and then pass the code ( say b2=31) to accounts\code ( say a2)

the problem i am having is the best i can get it to work only looks at similar entries and not the exact ones

I attach a spreadsheet

 

excel
Formulas and Functions

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 22, 2018

    Hi Robert,

     

    That could be

    =IFNA(INDEX(codes!$B$2:$B$1900,MATCH(1,INDEX(ISNUMBER(SEARCH(codes!$A$2:$A$1900,C2))*1,0,0),0)),"")

    It returns zero if the code is not found, for example you have code for 

    CARD PAYMENT TO TESCO STORES
but no code for
CARD PAYMENT TO TESCO STORE

    Attached.

Resources