VLOOKUP with IF OR returning N/A or BLANK

[UniqueUsername]

I have a table that due to sizing issues, I've split in 2 and placed side by side.

To simplify things, I'll use a Fruit/Price example. Column A&C are Fruit, Column B&D are the price of the fruit in the previous column.

I need to complete a VLOOKUP on the fruit to return the price. If the fruit is listed without a price or if the fruit isn't listed at all, I have additional calculations to complete.

My problem is trying to create a formula that checks whether the VLOOKUP produces an error or returns a blank.

I've tried
=IF(OR(ISNA(VLOOKUP("ORANGE", A2:B4,2,FALSE)),VLOOKUP("ORANGE",A2:B4,2,FALSE)),"TRUE","FALSE")

While the first part of the OR statement is true, ORANGE isn't found in the vlookup, the formula returns #N/A because the second part of the formula fails. How do I get the OR to stop calculating at the first "TRUE" statement?

Ultimately, the idea is to do the same search on C:D and complete other calculations if that fails as well.

Any ideas would be appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(x,XLOOKUP("orange",A2:A4,B2:B4,XLOOKUP("orange",C2:C4,D2:D4)),IF(ISNA(x),"not found",IF(x="","no data",x)))

 

[UniqueUsername]

Works beautifully! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[UniqueUsername]

Sadly, it works beautifully in Excel, but when I does not work in Google Sheets, which is my end goal and should have started in that direction.

Any ideas without the LET function?

 

[Fluff]

How about

=IF(ISNA(XLOOKUP("orange",A2:A4,B2:B4,XLOOKUP("orange",C2:C4,D2:D4))),"not found",IF(XLOOKUP("orange",A2:A4,B2:B4,XLOOKUP("orange",C2:C4,D2:D4))="","no data",XLOOKUP("orange",A2:A4,B2:B4,XLOOKUP("orange",C2:C4,D2:D4))))

It's always best to develop in the system you want to use. There are a lot of differences between Xl & Sheets.

 

[UniqueUsername]

Agreed! That worked great! Thanks again!

 

[Fluff]

Glad to help & thanks for the feedback.