+ Reply to Thread
Results 1 to 6 of 6

Xlookup make result blank if contains keyword

  1. #1
    Registered User
    Join Date
    03-17-2023
    Location
    Birmingham, England
    MS-Off Ver
    2016
    Posts
    9

    Xlookup make result blank if contains keyword

    HI,
    is it possible to get an xlookup to give you results as normal EXCEPT if the result contains a keyword listed in another cell to instead return a blank cell? I am trying to run xlookups but using keywords as an exclusion criteria.

    My xlookup formula so far is:
    =xlookup(E2&$I$1,'Raw data'!G:G,'Raw data'!B:B) and I want it to give a blank result if the xlookup result contains text found in G2, but if the xlookup result does not contain text in G2 then to show the result

    i.e. if my xlookup result is CAT when CAT is excluded (as listed in G2) then give blank cell, byt if my xlookup result was DOG then DOG would successfully be shown

    Any help much appreciated

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,110

    Re: Xlookup make result blank if contains keyword

    =if ( xlookup(E2&$I$1,'Raw data'!G:G,'Raw data'!B:B) = G2, xlookup(E2&$I$1,'Raw data'!G:G,'Raw data'!B:B) , "" )
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    03-17-2023
    Location
    Birmingham, England
    MS-Off Ver
    2016
    Posts
    9

    Re: Xlookup make result blank if contains keyword

    Many thanks- the code works the other way around i.e. it is using G2 to include i.e. report my result and excluding the others-I changed the = to <> and then it was the right way around.

    Is is possible rather than using exactly what G2 contains to search for a keyword in the xlookup result if my xlookup is a more complex alpha-numeric string e.g
    1. If G2 contains CAT then CAT-BAT-RAT search result would NOT show but RAT-BAT-HAT would?
    2. If G2 contains multiple terms e.g CAT OR HAT then it would exclude both search results above but include SAT-MAT3-FOG

    It is the first term in the string i.e CAT in CAT-BAT-RAT that needs to be check against G2 rather than anywhere in the text
    So, if G2 contained the CAT or HAT the below examples would happen

    CAT-BAT-RAT -excluded (i.e. blank as CAT at start)
    SAT-MAT3-FOG - include (search result shown)
    RAT-BAT-HAT - included (HAT is not the first term in the string)
    HAT-MAP - excluded (i.e. blank as HAT at start)

    Advice so far and if you can solve this last hurdle would be much appreciated!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,304

    Re: Xlookup make result blank if contains keyword

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,304

    Re: Xlookup make result blank if contains keyword

    Please can you confirm the version of Excel you are using and update your profile.

  6. #6
    Registered User
    Join Date
    03-17-2023
    Location
    Birmingham, England
    MS-Off Ver
    2016
    Posts
    9

    Re: Xlookup make result blank if contains keyword

    Hi,
    2016- I have attached a mock-up (hopefully it makes sense )

    Many thanks in advance
    Attached Files Attached Files

+ 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. [SOLVED] trying to use xlookup to get the result of looking at 2 columns
    By gjjh25 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-30-2022, 08:19 AM
  2. Xlookup returns a 0 (zero) instead of blank
    By AC101 in forum Excel General
    Replies: 2
    Last Post: 08-20-2021, 04:35 PM
  3. [SOLVED] Reformat to Make the Result BLANK IF FALSE
    By artiststevens in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-21-2020, 03:19 AM
  4. Replies: 6
    Last Post: 01-04-2017, 11:20 AM
  5. Replies: 2
    Last Post: 10-18-2013, 11:18 AM
  6. Filter List by Keyword & copy the result in column by keyword as header
    By kitunga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2013, 07:16 AM
  7. Nest SUM command with a command to make result cell blank
    By dwest185 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-17-2011, 08:10 PM

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