XLOOKUP with Nested IF formula

[janema]

I don't know I can't get this formula to work. This is doing an XLOOKUP against another file, to return the "Title" if there is one, and if the field is BLANK, then it returns "Review." However, if the person does not exist on the source file, it is coming back as an error "N/A" - Instead of the error, I am want it to come back blank, but not sure why my formula isn't work.

I started with an IF statement with the XLOOKUP to return the title from the source file, then if it was blank, it will return a "Review" - then I added that if it's an ERROR (because there is no data in the source) that it would return BLANK, but it keeps returning the error "#N/A." What am I doing wrong with the formula?

FORMULA:

=IF(XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD)="","Review",IFERROR(XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD),""))

 

[jasonb75]

If the first part returns an error then it will not look at the second part. You need to error trap the while thing. I've used LET as well to remove the need fire processing the lookup twice.

=LET(x,XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD),IFERROR(IF(x="","REVIEW",x),""))

 

[janema]

If the first part returns an error then it will not look at the second part. You need to error trap the while thing. I've used LET as well to remove the need fire processing the lookup twice.

=LET(x,XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD),IFERROR(IF(x="","REVIEW",x),""))

Thank you! I have never used the LET formula. I will have to learn that. When I bring down the formula to the bottom (so that it automatically populates when I enter new data), it all change to "REVIEW." Is there a fix for that? There is no LOOKUP value when I drag it all the way down, so that is probably why, but not sure why REVIEW would return though.

 

[jasonb75]

I don't personally use XLOOKUP so can't say for certain but it is possible that the empty cell for the lookup valuec is being matched to the empty cells at the bottle of the lookup column.

Simple fix here is to check if B4 is empty first.

=IF(B4="","",LET(x,XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD),IFERROR(IF(x="","REVIEW",x),"")))

 

[janema]

I don't personally use XLOOKUP so can't say for certain but it is possible that the empty cell for the lookup valuec is being matched to the empty cells at the bottle of the lookup column.

Simple fix here is to check if B4 is empty first.

=IF(B4="","",LET(x,XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD),IFERROR(IF(x="","REVIEW",x),"")))

Thank you so much! I will try that. I think that is going to be the solution. Appreciate your expertise so much!

 

[janema]

When I add the =IF(B4="","" to the front, I get an error. Is it in the wrong position? I can't seem to get it to work.

 

[jasonb75]

Did you put the extra closing bracket at the end as well?

 

[janema]

Did you put the extra closing bracket at the end as well?

I wrote it as follows:

=IF(B4="","",LET(x,XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD),IFERROR(IF(x="","REVIEW",x),"")))

Then tried:


=IF(B4="",""),LET(x,XLOOKUP(B4,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$B:$B,'[Promotion Recommendations -Final.xlsx]Promo Recommendations'!$AD:$AD),IFERROR(IF(x="","REVIEW",x),"")))

 

[jasonb75]

First one looks correct, what error are you getting?

 

[janema]

First one looks correct, what error are you getting?

I was missing ONE closed parenthesis at the end. I must have copied it incorrectly. Thank you so much! You ROCK!