+ Reply to Thread
Results 1 to 3 of 3

Combine Formula Search Value for Multiple Criteria

  1. #1
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    O365
    Posts
    36

    Combine Formula Search Value for Multiple Criteria

    Hi all,

    I have a pair of formulas to search for values based on multiple conditions. Can I combine both formulas and also simplify if possible ? Without array formulas or helper columns. if helps, attached is a file sample.

    Formula #1 - Search value in A3 and "High" in columns H and G respectively, if more than one instance is found then input "Multiple". If not, then look for value A3 and "High" (in same previous columns) and return value on column I. If values are not not found, then "N/A"
    =IFERROR(IF(COUNTIFS($H$2:$H$23,"*"&$A3&"*",$G$2:$G$23,"High")>1,"Multiple",VLOOKUP("High"&"*"&$A3&"*",CHOOSE({1,2},$G$2:$G$23&$H$2:$H$23,$I$2:$I$23),2,FALSE)),"N/A")

    Formula #2 - Same as formula #1, but with "Low" instead of "High"
    =IFERROR(IF(COUNTIFS($H$2:$H$23,"*"&$A3&"*",$G$2:$G$23,"Low")>1,"Multiple",VLOOKUP("Low"&"*"&$A3&"*",CHOOSE({1,2},$G$2:$G$23&$H$2:$H$23,$I$2:$I$23),2,FALSE)),"N/A")


    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Combine Formula Search Value for Multiple Criteria

    If you're trying to eliminate columns D:E, then try:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    O365
    Posts
    36

    Re: Combine Formula Search Value for Multiple Criteria

    That's what I needed. The only change is that I replaced A3 for "*"&$A3&"*" so it can find the ID value within column H text strings.

    Thank you!

+ 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. Replies: 0
    Last Post: 04-30-2020, 03:20 PM
  2. Replies: 2
    Last Post: 11-12-2019, 08:22 PM
  3. Create User form to perform search using multiple search criteria
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-29-2015, 06:16 AM
  4. Replies: 7
    Last Post: 06-28-2015, 10:47 AM
  5. Replies: 4
    Last Post: 05-20-2014, 10:03 AM
  6. Search Multiple Worksheets Against List of Non-Exact Search Criteria?
    By thump4r in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2010, 03:46 PM
  7. [SOLVED] Search For Multiple Entries - Combine and Sum
    By Ledge in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2006, 12:35 PM

Tags for this Thread

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