Forum Discussion
Xlookup with nested IF
Thank you in advance for your help.
What I am trying to do is return a value (using Xlookup) but only if another value matches. Maybe I need to use Index/Match, but here's my example:
Col A Col B Col C Col D
Yes John Smith 1.50
No John Smith 1.25
So, I'm trying to enter an Xlookup that will search for "Smith" in column C and return Col D, but only if Col A is also "Yes". I tried =XLOOKUP("Smith",$C:$C,IF($A3="yes",$D:$D,"-"),"-") but I get a "#Value" error. For real life application, I would be replacing "Smith" with a cell reference and "yes" with another cell reference. But for the example, if A=yes, then return D if Col C=Smith. I just need to figure out in which order xlookup (or index/match) would look for those.
Any help is appreciated.
5 Replies
A more general approach, available in Excel 365, is to define a Lambda function to do whatever you require. For example
ARRLOOKUP位 = LAMBDA(lookup_values, lookup_array, return_array, XLOOKUP( TRUE, BYROW(lookup_array = lookup_values, AND), return_array ) );will allow you to lookup a row of values in the corresponding range of the data array using a formula
= ARRLOOKUP位(selectedValues, HSTACK(answer, name), value)where, in this instance, selectedValues is a 2 cell range containing "Yes" and "Smith".
From there, the task just tends to grow. Maybe you might want unspecified cells in the lookup values to match any value from the lookup array; and so it goes on!馃槄
= XLOOKUP("Smith", IF(answer="Yes", name), value, "Name not found")This formula replaces names in the search array by FALSE if the other condition/s are not satisfied.
[Note: I have also introduced defined names because I treat direct references as qualitative errors, but that is just me]
As variant
=XLOOKUP( $G$2 & "=" & $G$3, C:C & "=" & A:A, D:D, "-" )=XLOOKUP(1,($A$1:$A$2="YES")*($C$1:$C$2="SMITH"),$D$1:$D$2) In This Formula i Had Used Lookup Value as 1 and in Lookup Array i had used ($A$1:$A$2="YES")*($C$1:$C$2="SMITH") this will give result in 0,1,0,1 format and where value is match with 1 , Xlookup will go to same row but column D and give you the result, in Place of Smith you can use Cell Ref
=FILTER($D$1:$D$1000, ($A$1:$A$1000="Yes")*($C$1:$C$1000="Smith"), "")
or
=INDEX($D$1:$D$1000, XMATCH(1, ($A$1:$A$1000="Yes")*($C$1:$C$1000="Smith")))
