Conditional Formula If/then, vlookup False return isn't working

Traveltime

New Member
Joined
May 21, 2018
Messages
17
I hope someone can assist with this, it's probably fairly basic. I'm a beginner with formulas so any help is very much appreciated.

This is what I'm trying to accomplish:
If the text in AN equals one of the text values in a separate column, then I want to enter the cell to equal the text in cell AN, if not I want the cell to equal the text in cell C.

This formula works for the true statement, but I receive "#N/A" for the false statement.

=IF(AN2=(Vlookup(AN2,F2:F32,1,false)),AN2,C2)


Array Table:
Sub Expense Type-draft (Keep)
Airport Fee
Parking
Other Ground T
Baggage Fees
Airfare
Rail
Taxi/Car Servi
Tolls
Airfare
Hotel
Hotel Tax
Hotel Deposit
Car Rental
Fuel
Miscellaneous
Tips
Expense Distribution
Transport
Transportation
Passengers
Immunizations/
Meals - Overni
Meals - Same D
Personal - Cor
Travel Suspense
Entertainment
PA Vouchers
NC Catering
PA Catering
AZ Catering
AZ Vouchers
<colgroup><col width="199" style="width: 149pt; mso-width-source: userset; mso-width-alt: 7082;"> <tbody> </tbody>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
See if this works for you:

=IF(ISNUMBER(MATCH(AN2, $F$2:$F$32, 0)), AN2, C2)
 
Upvote 0
It worked, thank you so much!

I have been working on this for days. I will read up on how those formulas work so I know in the future.

Do you know why my formula failed to return the value in cell C2?
 
Upvote 0
When using the VLOOKUP() function with the FALSE (meaning exact match parameter) does not find a match, it returns an error (#N/A). Once the IF function gets an error inside, it will return the error, and not evaluate the second or third parameter of the IF function.

So, essentially, this is what happens:

Rich (BB code):
=IF(AN2=(Vlookup(AN2,F2:F32,1,false)), AN2, C2)
=IF(AN2=#N/A, AN2, C2)
=#N/A

I know it is somewhat illogical, but that is how the IF function is designed to work.

The MATCH version checks for the error implicitly, by testing whether the result is a number. So, this happens:

Rich (BB code):
=IF(ISNUMBER(MATCH(AN2, $F$2:$F$32, 0)), AN2, C2) 
=IF(ISNUMBER(#N/A), AN2, C2) 
=IF(FALSE, AN2, C2) 
=C2

Hope that makes sense.
 
Last edited:
Upvote 0
I hope someone can assist with this, it's probably fairly basic. I'm a beginner with formulas so any help is very much appreciated.

This is what I'm trying to accomplish:
If the text in AN equals one of the text values in a separate column, then I want to enter the cell to equal the text in cell AN, if not I want the cell to equal the text in cell C.

This formula works for the true statement, but I receive "#N/A" for the false statement.

=IF(AN2=(Vlookup(AN2,F2:F32,1,false)),AN2,C2)

[...]

Invoking MATCH with an ISNUMBER test is the obvious thing to do.

But it's also possible with VLOOKUP with an appropriate result test added.

=IF(AN2=IFERROR(VLOOKUP(AN2,F2:F32,1,FALSE),""),AN2,C2)

Or with IFNA if available...

=IF(AN2=IFNA(VLOOKUP(AN2,F2:F32,1,FALSE),""),AN2,C2)
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top