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)))

 

[hagia_sofia]

If a value to be returned via XLOOKUP search is blank, XLOOKUP will return it as 0; so, instead of ="" you need to wrap the whole XLOOKUP into ISBLANK function, i.e.

IF(ISBLANK(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)...

 

[Fluff]

That formula should work. Can you post some of the data from the LS 4.2024 sheet.

 

[janema]

If a value to be returned via XLOOKUP search is blank, XLOOKUP will return it as 0; so, instead of ="" you need to wrap the whole XLOOKUP into ISBLANK function, i.e.

IF(ISBLANK(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)...

Like so?

=IF(AS2="","",IF(ISBLANK(XLOOKUP(AS2,'LS 4.2024'!L:L,'LS 4.2024'!P:P)="",IF(ISBLANK(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]

There is no need to use isblank, especially as it may not work.

 

[janema]

That formula should work. Can you post some of the data from the LS 4.2024 sheet.

Sure. Here is an example:

The data does NOT exist in the LS 4.2024 sheet:

But it does exist in the All US sheet:

However; it is returning a BLANK or ZERO in the formula.

 

[Fluff]

Can't tell much from an image, but column P is not empty. It has either a 0 (formatted to show "-"), or an "-" so you need to check for that.

 

[janema]

Can't tell much from an image, but column P is not empty. It has either a 0 (formatted to show "-"), or an "-" so you need to check for that.

It is empty or doesn't exist on the "LS 4.2024" file so shouldn't it then pull from the "All US" file in my formula?

 

[Fluff]

The cells in col P are NOT empty.

 

[janema]

The cells in col P are NOT empty.

The lookup in my example is EN.ESCA.P5, but that does not exist in the LS 4.2024 file as shown in the image so I wanted it to pull from the ALL US file. I think you mean that using IF="" is incorrect because it's not blank, it's just non-existent?