Using IS Number and Search function

Hi all

This is my first post and hoping someone can help me out,

I am trying to workout a formula to look-up and categorize data from a bank statement, without having to complete manually monthly.

So far I have a formula that returns the amount spent, but would need an extra column and separate table for each category.

IF(OR(ISNUMBER(SEARCH(" "&$G$3:$G$11&" "," "&A2&" "))),B2,0) where


Instead of returning B2, I would like to return a value from the table in column H3:H11 that corresponds with G2:G11, as shown below.

ColumnG ColumnH
Aldi Food
woolworths Food
Coles Food
saint Nicholas School
Blooms Medicines, toiletries, hairdressing, personal items
vodafone telephone

So for example if Aldi is in the text in A2, then I would like to return food ( from a lookup table similar to above) into the column where my formulae is ( say F2).

Appreciate any help, Thanks, Marie.

hi attach a Sample workbook

Punnam

sample.xlsx


HI all,

Please see attached example. Sorry should have attached this to the first post.

Hi,

This formula would work if the data in column A is exactly the same as in column G. (as is the case for some of my data)

In my case I want to do a search for keywords in A that are the same as G.

e.g Column A might have Direct Debit 119218 VODAFONE and the look-up table has VODAFONE. using vlookup this will not return a value.

Hi,


Please find the attached file,

And reply is that works fine for you or not,

once your thread solved mark it as solved.

Rajan

Thank you MartindWilson. That works perfectly and will save me hours of trying to do it manually.

Glad I posted to the forum.