+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    England
    MS-Off Ver
    2013
    Posts
    2

    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. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-11-2019
    Location
    England
    MS-Off Ver
    2013
    Posts
    2

    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. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compare multiple columns (column A,column B,Column C) and return value (Column D)
    By john008 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2015, 08:24 AM
  2. Replies: 3
    Last Post: 02-11-2014, 12:41 PM
  3. Return the Title of a column if the column is the highest column with data
    By williamspage in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-22-2013, 10:05 AM
  4. [SOLVED] Search Value of column E if exist return value of column B put result in column P
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-23-2013, 11:39 AM
  5. [SOLVED] IF Column A contains text in Column B then return corresponding number in C in Column D?
    By liawsiqin@gmail in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-29-2012, 02:36 AM
  6. [SOLVED] Lookup and Match Column A with Column B and return Value from Column C
    By Mikeytres in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-28-2012, 12:19 PM
  7. Replies: 3
    Last Post: 01-10-2006, 06:10 AM

Bookmarks

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