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)

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]

      • HLowell's avatar
        HLowell
        Copper Contributor
        Aug 21, 2023
        Appologies, no. The [if_not_found] values show up as 0 instead of searching the second Column (F). I am having difficulties figuring out how to return a different value when Column A fails the cross-reference.