Lookup but if No Return (#N/A) then use IFs

[Stebrownsword1986]

hi all, thanks in advance

I hope this makes sense, I need my formula to do a VLOOKUP but if it comes back with NA, I want it to IFs based on the following

So the vlookup would be
=VLOOKUP(A2,Lookups!A:C,3,FALSE)

but if it comes back as N/A, I want a IF formulas based on the following 3 IFs
1) IF Cell F2 includes Medical and Dental and cell W2 is Less than 0.69999 Then Return as "Remove"
2) IF Cell H2 contains "Chair" then Return as "Remove"
3) IF the lookup doesn't pick them up and they don't meet the above 2 IFs, then the Return to state "Keep"

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(Return,VLOOKUP(A2,Lookups!A:C,3,FALSE),IF(NOT(ISNA(Return)),Return,IF(OR(AND(F2="Medical and Dental",W2<0.69999),H2="Chair"),"Remove","Keep")))

 

[Stebrownsword1986]

=LET(Return,VLOOKUP(A2,Lookups!A:C,3,FALSE),IF(NOT(ISNA(Return)),Return,IF(OR(AND(F2="Medical and Dental",W2<0.69999),H2="Chair"),"Remove","Keep")))

Fantastic, worked great!

 

[Fluff]

You're welcome & thanks for the feedback.