Forum Discussion

lesasp's avatar
lesasp
Copper Contributor
May 27, 2021
Solved

Xlookup - If_not_found not working

Hi, I have created the following formula using XLOOKUP, however, the if_not_found does not seem to be working correctly, continuing to return a N/A, instead of a 'zero' if the value is not present in...
excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    May 27, 2021

    lesasp 

    How about

     

    =IFERROR(SUM(XLOOKUP(J3,'M&E Data'!A:A,'M&E Data'!B:B):XLOOKUP(Calculations!J3,'M&E Data'!A:A,XLOOKUP(BSC!$V$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M),0)),0)

HansVogelaar's avatar
HansVogelaar
MVP
May 27, 2021

lesasp 

The 0 is the if-not-found argument of the inner XLOOKUP. You don't specify if-not-found for the outer XLOOKUP.

I don't understand why you use XLOOKUP(BSC!$C$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M,0) as return_array - it produces a single value. But try

 

=XLOOKUP(J6,'M&E Data'!A:A,XLOOKUP(BSC!$C$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M,0),0)

HLowell's avatar
HLowell
Copper Contributor
Aug 21, 2023

HansVogelaar =XLOOKUP(TRUE,ISNUMBER(SEARCH(AMAZONDATA!A:A,C8)),AMAZONDATA!B:B,XLOOKUP(TRUE,ISNUMBER(SEARCH(AMAZONDATA!F:F,C8)),AMAZONDATA!B:B,""))

For the [if_not_found] section, I want the formula to reattempt the search in a different column (F) and return that data.  Is this incorrect?

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 21, 2023

    HLowell 

    Try this version:

     

    =XLOOKUP(C8, AMAZONDATA!A:A, AMAZONDATA!B:B, XLOOKUP(C8, AMAZONDATA!F:F, AMAZONDATA!B:B, ""))

    • HLowell's avatar
      HLowell
      Copper Contributor
      Aug 21, 2023

      HansVogelaar Yeah, I had to keep it "dirty" as the values in the AMAZONDATA! sheet contain less characters than the Values being referenced on the main sheet.  Imported data has the apostrophe to indicate text follows, so it's not an exact match reference.  That's how I landed on the (TRUE,ISNUMBER(SEARCH( )) for [lookup_value][lookup_array]