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

[Traveltime]

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)


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

 

[iliace]

See if this works for you:

=IF(ISNUMBER(MATCH(AN2, $F$2:$F$32, 0)), AN2, C2)

 

[Traveltime]

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?

 

[Traveltime]

Whoops, I mean, "return the value from cell C2?"

 

[iliace]

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:

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

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

 

[Aladin Akyurek]

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)

 

[Traveltime]

It sure does. Thanks for the detailed explanation.

 

[Traveltime]

Good to know. Thanks