# If x,y or z in one column return b in another column

1. ## If x,y or z in one column return b in another column

I have a spreadsheet of bank transactions and want to categorise them by type of shop. I'm trying to write a formula that would do something like the following for each transaction in the sheet: If the words 'asda, waitrose or tesco' are in column A write 'supermarket' in column B, If the words 'electric or phone' are in column A write 'utility' in column B, (and so on until I've covered all key words / categories).
Every month I would download that months bank transactions, add them to the bottom of the list and it would categorise the new ones.
Ideally the formula would be looking up the answers using a separate sheet so I can easily keep adding new shops / categories in the future.

I hope that makes sense! Thanks

2. ## Re: If x,y or z in one column return b in another column

Welcome to the forum!

Yes - you need a categories lookup table that looks like this:

Excel 2016 (Windows) 32 bit
A
B
1
Asda Supermarket
2
Waitrose Supermarket
3
Tesco Supermarket
4
Electric Utility
5
Phone Utility
 Sheet: Sheet1

This could be on a hidden tab. All you then need is this:

=VLOOKUP(cell_containing_data,\$A\$1:\$B\$5,2,0)

3. ## Re: If x,y or z in one column return b in another column

Hi there,

See if the attached workbook gets you moving in the right direction.

The data in the "Categories" worksheet can be added to as more companies appear on your "Financial" worksheet. The formula in Column C of the "Financial" worksheet will display the Category appropriate to the Company entered in Column B.

Hope this helps - please let me know how you get on.

Regards,

Greg M

4. ## Re: If x,y or z in one column return b in another column

Thank you both for your speedy responses! I've tested it out and realised there's an added complication and I over simplified my examples! In the raw data it won't say just 'tesco' but maybe 'tesco 23232323' one day or 'tesco express' another day. Is there a way of adding a wildcard to the formula / look up table so I'm asking it to look for 'anything containing tesco'?

5. ## Re: If x,y or z in one column return b in another column

Yes - this should work:

=VLOOKUP(LEFT(cell_containing_data,FIND(" ",cell_containing_data)-1),\$A\$1:\$B\$5,2,0)

@Greg - why is your workbook macro-enabled?

6. ## Re: If x,y or z in one column return b in another column

Hi Ali,

In this case it doesn't need to be, but as about 99% of my workbooks contain macros, the default setting I use for Save Format is Excel Macro-Enabled Workbook

Thanks for all of your work and contributions on here.

Best regards,

Greg M

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1