How to Return Text instead of N/A when value is not found XLOOKUP

[JulieBunavicz]

Hi,

Any idea how to return "Not Found" instead of N/A when value is not found XLOOKUP?

Kind regards,

Julie Bunavicz

 

[Joe4]

One way would be to wrap yopur current XLOOKUP formula in an IFERROR function, i.e.

=IFERROR(XLOOKUP(...),"Not Found")

 

[kweaver]

Why wouldn't you use the 3rd argument of XLOOKUP?

 

[Joe4]

Why wouldn't you use the 3rd argument of XLOOKUP?

Ha! You can tell how much I use XLOOKUP. I am so used to the old VLOOKUP and LOOKUP formulas. I forgot that XLOOKUP make it super easy for you now by baking it right into the formula!
See: XLOOKUP function - Microsoft Support

 

[JulieBunavicz]

Awesome!! IFERROR worked I had been trying ISNA and also IF "N/A" then.... neither were exactly what I was looking for. I appreciate your responses. Thanks!!

Formula in the Test Lookup column =IFERROR(XLOOKUP(D2,A:A,B:B),"Not Found")

Test Num	Animal		Test Num	Test Lookup
1​	cat		1​	cat
2​	dog		2​	dog
3​	bird		3​	bird
4​	cat		4​	cat
5​	bird		5​	bird
			6​	Not Found

I XLOOKUP

 

[kweaver]

Did you try: =XLOOKUP(D2,A:A,B:B,"Not Found")

 

[Joe4]

As kweaver indicates, it is totally unnecessary to use IFERROR, as XLOOKUP already has a "Not Found" argument built right into it.
So I think their formula should work for you.

 

[JulieBunavicz]

Did you try: =XLOOKUP(D2,A:A,B:B,"Not Found")

That works too. Thanks!!