+ 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
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    25,389

    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!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    Posts
    2,619

    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
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    25,389

    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
    Posts
    2,619

    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)

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