Search code examples
excelexcel-formulaxlookup

xlookup formula with more than one criteria

i am trying to match a value using xlookup formula to find I1 in A and if H1>C and H1<D return B i have below this but cant figure out how to make it work =XLOOKUP(I1,A:A,B:B,,IF(AND(H1>C,H1<D),B:B,"none"))

i have an index and match that works but im trying to learn with xlookup with multiple critera =IFERROR(INDEX(B:B,MATCH(1,(C:C<H1)*(D:D>H1)*(A:A=I1),0)),"none")

update

well my bad excel 2016 does not support xlookup

uploaded sample data G1 is where my index match formula is G2 is the xlookup

enter image description here

Solution

  • You can use the same logic as your INDEX/MATCH solution:

    =XLOOKUP(1,
         (C:C<H1)*(D:D>H1)*(A:A=I1),
         B:B,
         "none",
         0)

    Where, just like with your MATCH 1 stands for TRUE and it looks up the first row where all the arrays return TRUE, then returns the value in that row from column B:B or if nothing's found it returns the built in IFERROR value: none.

    enter image description here