Returning #VALUE when using IF condition for Vlookup

[soumendu_bose]

Returing #value when using IF condition for Vlookup. Giving below exmaple.

Col. A Col. B Col. C Col. D (Match Result)
Row1 A1 AAA A2 #VALUE!
Row2 A2 BBB A6 #N/A
Row3 A3 CCC
Row4 A4 DDD
Row5 A5 EEE
I want to write the value of col. B into Col. D by using the below formula if the value of Col. C matches with the value of Col. A.
=IF(VLOOKUP(C1,$A$1:$B$5,1,FALSE),B1)

When both Col. A and Col. C values are numeric then the formula works but when the value is text result returns #VALUE!

Please help me.

[Richard Buttrey]

Hi,

Try instead

Please Login or Register  to view this content.

If you note that with your original formula whichever numeric you use you will always return the B1 value. That's because your Vlookup returns a numeric and the IF test interprets a numeric as True and hence returns the B1 value - you have no False argument in your IF test.

With alpha characters in the Vlookup then the IF test doesn't evaluate to True or False, hence the error you see.

[soumendu_bose]

Thanks a lot Richard... it's working

[WNErika]

Just a tip I have found useful when using the "IFERROR" type function...rather than using a blank, I will have it report back a character so i can see that the function is in play. Examples could be "-" or "." Richard's answer is spot on and very useful with any such issues you run into when you get the dratted #N/A or #VALUE; just thought i would throw my two cents in.

[Fotis1991]

soumendu_bose

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.