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!
Bookmarks