1

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

k1dr0ck
  • 1,043
  • 4
  • 13
  • 1
    @P.b's formula works for me, returning "none" whether I use `1` or `I1` as the lookup reference - if you get rid of the full column references, you will likely get to the root of the problem if you select the `(C1:C12

    H1)*(A1:A12=I1)` part of the formula and press F9

     – Spectral Instance Mar 04 '23 at 13:37
  • @SpectralInstance tried your suggestions still no luck also suggestions of P.bs – k1dr0ck Mar 04 '23 at 14:48

1 Answers1

3

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

P.b
  • 8,293
  • 2
  • 10
  • 25
  • tried it but still have error value,i even used ctrl+shift+enter, uploaded pic for sample data – k1dr0ck Mar 04 '23 at 13:04
  • Added screenshot of the formula in action with the data you shared in the screenshot (better to share using markdown table). It's probably a trailing space or character in the search value or the search column, or the dates are not dates, but text. Also it's better to use `<=` and `>=` to include dates that equal listed dates. – P.b Mar 04 '23 at 14:18
  • i checked the values those are dates and also no leading or trailing spaces used the suggestion by Spectral Instance and yours too still no luck, maybe its coz im using excel 2016? what do you mean by markdown table? – k1dr0ck Mar 04 '23 at 14:32
  • 1
    Excel 2016 does not support XLOOKUP – P.b Mar 04 '23 at 14:53
  • my bad, still accepted your answer, if i type "=" then "x" the xlookup function appears so i assumed it supported excel 2016 :) – k1dr0ck Mar 04 '23 at 14:58
  • Your index match solution is a good alternative. That it appears surprises me. https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 – P.b Mar 04 '23 at 15:03