Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

#Value Error If Statement

0

Trying to do a simple If statement and i'm getting a "Value! error in column H?

Formula in H - Trying to return the value in column C to the tab "END Match" - Column A.

If statement matches = "END", if it does not match return (" ") blank cell. 

I've tried trimming and changing to text. Also tried this "=IF(C2='END Match'!A:A,"END","")

It's so simple but yet cannot figure out.

Answer
Discuss

Answers

1
Selected Answer

The purpose of VLOOKUP is to look up a value in one column and return a corresponding value from another. Like a telephone book where you look up a name and return the number written next to it. To check if a value exists in a column you would better use MATCH. The formula below checks if a match is returned and displays "END". If there is no match MATCH will return a #N/A error for which you can check using the ISNA() function.

=IF(ISNA(MATCH(TRIM(A2),'END Match'!A:A,0)),"","END")

However, you could achieve the same result with VLOOKUP. Here is the formula.

=IF(LEN(IFERROR(VLOOKUP(TRIM(A2),'END Match'!A:A,1,FALSE),"")),"END")

The basic VLOOKUP(TRIM(A2),'END Match'!A:A,1,FALSE) is embedded in an ISERROR function which converts the result to "" if no match is found. But if VLOOKUP does return a value (from column 1 of the lookup range) it would be text, not a number. Therefore you can't use IF(VLOOKUP(TRIM(A2),'END Match'!A:A,1,0),"END","") which asks if the result is a number different from zero. Wel, it isn' at all and that is the likely source of the Type error you received. To refresh, this syntax is equivalent to IF(VLOOKUP(TRIM(A2),'END Match'!A:A,1,0)<>0,"END","") where the <>0 can be omitted because it is implied.

In your workbook the result is never a number. Best case it is a string that evaluates to a number. Therefore my formula tests the length of the result. Of course, the length is always a number. Therefore the <>0 can be omitted. Depending upon the result of the Len() function, either "END" or "" is displayed. The formula is longer because, as I said above, VLOOKUP isn't really intended for this kind of use.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login