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

#### Stebrownsword1986

##### New Member

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"

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi & welcome to MrExcel.
Excel Formula:
``=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")))``

=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!

You're welcome & thanks for the feedback.

Replies
3
Views
1K
Replies
1
Views
313
Replies
3
Views
849
Replies
0
Views
389
Replies
1
Views
437

1,203,501
Messages
6,055,765
Members
444,821
Latest member
Shameer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back