Using IFNA with 2 Nested VLOOKUPs

[The Incendiary]

Hello, all. I've been working on a tool that I want to be able to VLOOKUP a value from a range, then if the answer is "#N/A," search a different range. I also want this cell to not show its result until data is entered in the "data" cell. I tried several combinations of nested IFNAs and IFs along with VLOOKUP, and I got close but wasn't able to get what I needed. Here's a generic version of what I have:

Data cell: B4
In B2 (this is the intended nested "check" cell): =IFNA(VLOOKUP(B4,'filename.xlsx$Range$,col_index_num,0),"")
In C2 (this is the extra "helper" cell I had to add since the nesting wasn't working): =IF(COUNTIF(B2,"<>"),VLOOKUP(B4,'2ndfilename.xlsx$Range$,col_index_num,,0),"")

The logic here that I ultimately want is:
Data is entered in B4 (B2 is blank until this happens)
B2 checks the data in B4 against one data source with VLOOKUP to return a matching value
If this =#N/A, it checks another source (called "2ndfilename" above), also with VLOOKUP
If this =#N/A, 0, etc., a message is returned

Does anyone have any insight as to how I might accomplish this, all within the one "check" cell, B2? Thanks for any help you can provide!

 

[SanjayGMusafir]

With Excel 365 in your hand you can do same with XLOOKUP. It has got in built option of [If not found]

 

[The Incendiary]

With Excel 365 in your hand you can do same with XLOOKUP. It has got in built option of [If not found]

So XLOOKUP once, then another XLOOKUP if not found (then another conditional statement to put the message text)? I tried that once, but did not have success either

 

[SanjayGMusafir]

I tried that once, but did not have success either

Success could be because of other factors. I have used XLOOKUP(s) up to 4 seeded inside one and it worked perfect. Don't know how many can you seed within one...

 

[The Incendiary]

Success could be because of other factors. I have used XLOOKUP(s) up to 4 seeded inside one and it worked perfect. Don't know how many can you seed within one...

Thanks! I was actually able to make this work with the below setup:

=IF(ISBLANK(data cell),"",IFNA(XLOOKUP),XLOOKUP,"Not found","Not found")

 

[SanjayGMusafir]

=IF(ISBLANK(data cell),"",IFNA(XLOOKUP),XLOOKUP,"Not found","Not found")

Just in case it interests you, there can be a shorter version of it. This way you have to write the XLOOKUP statement only once.

Check this and revert -

=Let(XL,XLOOKUP Statement,IFS(data cell="","",True,XL))

"Not Found" can be part of XLOOKUP statement itself...