Nested IF with XLOOKUP formula

[janema]

I am trying to get this formula to return BLANK if cell AS2 is blank, and if the first XLOOKUP is Blank or no data, to use the second XLOOKUP, otherwise keep the first XLOOKUP. For some reason, it is only working with the first XLOOKUP. Can someone please help me figure out why the formula isn't working? Thank you so much in advance!

The formula is currently written as follow and shown below:

=IF(AS2="","",IF(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)="",XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)))

 

[Alex Blakenburg]

Start with making this change:

=IF(AS2="","",IF(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P,"")="",XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)))

Fluff's point if what do you want to do if it exists in LS 4.2024 but the value is 0.
Since its likely that the dashes there are 0

Also what version of Excel do you need the solution to work on, if you only need it to work on 365 or 2021 we can use the Let function to simplify it.

 

[janema]

Start with making this change:

=IF(AS2="","",IF(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P,"")="",XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)))

Fluff's point if what do you want to do if it exists in LS 4.2024 but the value is 0.
Since its likely that the dashes there are 0

View attachment 114709

Also what version of Excel do you need the solution to work on, if you only need it to work on 365 or 2021 we can use the Let function to simplify it.

Ohhhh, I am so sorry! If there is a value, but it is 0, then I also want it to take from the other sheet the "All US 4.2024."

 

[janema]

Start with making this change:

=IF(AS2="","",IF(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P,"")="",XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)))

Fluff's point if what do you want to do if it exists in LS 4.2024 but the value is 0.
Since its likely that the dashes there are 0

View attachment 114709

Also what version of Excel do you need the solution to work on, if you only need it to work on 365 or 2021 we can use the Let function to simplify it.

Forgot to answer your last question. It is on 365.

 

[Alex Blakenburg]

Thanks for the responses, give this a try.
You will need to copy it directly into the formula box on row 2

=LET(getLS,XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P,0),
     IF(AS2="",
        "",
        IF(getLS=0,
           XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),
           getLS)))

Using Let will save performing the LS lookup twice where it needs to.
In terms of your original formula the key change is in red below:

=IF(AS2="","",IF(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P,0)=0,XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)))

 

[janema]

Thanks for the responses, give this a try.
You will need to copy it directly into the formula box on row 2

=LET(getLS,XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P,0),
     IF(AS2="",
        "",
        IF(getLS=0,
           XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),
           getLS)))

Using Let will save performing the LS lookup twice where it needs to.
In terms of your original formula the key change is in red below:

=IF(AS2="","",IF(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P,0)=0,XLOOKUP(AS2,'All US 4.2024'!L:L,'All US 4.2024'!P:P),XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)))

Thank you so much!!

 

[Alex Blakenburg]

You're welcome. Glad we could help.