ADDING LOGIC TO AN EXISTING FORMULA

[meppwc]

For the following formula
=IFERROR(XLOOKUP(F2,Sheet2!A:A,Sheet2!B:B),"")
Currently, if the value is found in A:A, and if B:B cell is blank, the formula returns zero "0"
When zero is the result, I would like it to return "STATUS IN AL IS UNDETERMINED"

I keep trying to get that result and I am sure that my syntax is incorrect. I would greatly appreciate some assistance.

 

[Joe4]

How about:

=LET(x,IFERROR(XLOOKUP(F2,Sheet2!A:A,Sheet2!B:B),""),IF(x=0,"STATUS IN AL IS UNDETERMINED",x))

 

[meppwc]

Thanks Again Joe4...........I was going about this completely the wrong way.

 

[Joe4]

You are welcome.

I don't know if you have used the LET function before, but it is a great new function that let's you set a whole formula equal to a variable, so you can reference it more than once in your calculation without having to retype the whole thing. Really useful in cases like these!

Here is more about it: LET function - Microsoft Support

 

[meppwc]

I took good notes from your example...........I am sure I will be using LET in the future

 

[meppwc]

Hi Joe4
Recently you assisted me (see above), with an alternative for "0" if cell B:B is blank. ("STATUS IN AL IS UNDETERMINED")
What if I want additional logic if either B:B or A:A is blank
If A:A is blank, then "VALUE SEARCHED ON NOT FOUND"

 

[Joe4]

You just need to update the XLOOKUP portion of the formula, as it has an option of what to return if no matching value is found.
(See: XLOOKUP function - Microsoft Support)

You are already telling it to return "", so you would just then need to change that part of the formula like this:

=LET(x,IFERROR(XLOOKUP(F2,Sheet2!A:A,Sheet2!B:B),"VALUE SEARCHED ON NOT FOUND"),IF(x=0,"STATUS IN AL IS UNDETERMINED",x))

 

[meppwc]

oh.........that was very obvious.........sorry for the bother, but appreciate your help once again.

 

[Joe4]

You are welcome.