IFERROR, XLOOKUP returns 0 instead of value

[j_saints]

Hello,

I was hoping someone could help me fix this combination function below.

I have a company table and I want to lookup their parent company based on their keywords.

I'm trying to return "OTHER ACCOUNTS" if the lookup match is blank/error. For some reason, it's returning to 0.

=IFERROR(XLOOKUP(TRUE,ISNUMBER(SEARCH('PARENT COMPANY'!$A:$A,'COMPANY TABLE'!B1501)),'PARENT COMPANY'!$C:$C),"OTHER ACCOUNTS")

Any help is much appreciated!

 

[steve the fish]

Switch the ranges in the SEARCH formula. That is the find text should be B1501.

 

[Tivakaran]

Try:
IFERROR(XLOOKUP(TRUE,ISNUMBER(SEARCH('PARENT COMPANY'!$A:$A,'COMPANY TABLE'!B1501)),'PARENT COMPANY'!$C:$C),),"OTHER ACCOUNTS")

 

[jasonb75]

Maybe

=XLOOKUP("*"&'COMPANY TABLE'!B1501&"*",'PARENT COMPANY'!$A:$A,'PARENT COMPANY'!$C:$C,"OTHER ACCOUNTS",2)

 

[Alex Blakenburg]

If you want an exact match but to return "OTHER ACCOUNT" in both of the following:

• Not Found
• Found but C is blank

Then this should work:-

=XLOOKUP(1,ISNUMBER(SEARCH('COMPANY TABLE'!B1501,'PARENT COMPANY'!$A:$A))*('PARENT COMPANY'!$C:$C<>""),'PARENT COMPANY'!$C:$C,"OTHER ACCOUNTS")

 

[jasonb75]

Good spot, Alex!

I hadn't considered the lookup result being an empty cell. Trying to do it without arrays, although this is still on the assumption that the observation made in post 2 is correct.

=LET(x,XLOOKUP("*"&'COMPANY TABLE'!B1501&"*",'PARENT COMPANY'!$A:$A,'PARENT COMPANY'!$C:$C,0,2),IF(x=0,"Other Accounts",x))