If with Vlookup

[pedroc]

Hi all,

I have the following function

=IF(B36=VLOOKUP(B36,Sheet2!$A$1:$A$60,1,TRUE),"Yes","X")

the function has been copied across a number of cells so that when a correct answer listed in the range on sheet 2 is entered a "Yes" should appear in column C.

However this function seems to randomly returning the "X" value even when the answer is correct. and on other occasions is returning nothing at all.

Have tried changing the true statement to false but this appears to be just as random.

Can anyone suggest a fix for this or an alternative function.

Many thanks

pedroc

 

[MrVillareal]

Hi please provide data source and also your expected result. Provide a simple sample.

 

[prajul89]

Change True to False in Vlookup.
I know you have already done it as you say but at the movement that seems to be the only problem.
And what do you mean it returns nothing at all?
Does it returns #N/A or just completely blank?

 

[prajul89]

I think what you need is Isna formula. Try this:


=IF(ISNA(VLOOKUP(B36,Sheet2!$A$1:$A$60,1,FALSE)),"X","YES")

 

[pedroc]

Change True to False in Vlookup.
I know you have already done it as you say but at the movement that seems to be the only problem.
And what do you mean it returns nothing at all?
Does it returns #N/A or just completely blank?

Sorry - yes it returns N/A

 

[pedroc]

Hi please provide data source and also your expected result. Provide a simple sample.

Hi Villareal

the data source is just a list of 60 correct answers stored on sheet 2 A1:A60.

this function has been placed in column C on sheet 1 in the cell next to where a user can input their answer. the result being a Yes displayed if they have the correct answer and a X displayed if they are incorrect.

 

[MrVillareal]

Is there any duplicate values in table array under the Vlookup? If that is the case, you will not get the right answer. For ex.

Excel Workbook
	C	D	E	F
30	LookupTable		LookupValue	ReturnValue
31	1		1	1
32	2
33	3
34	1
35	2
36	3
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F31	=VLOOKUP(E31,C31:C36,1,FALSE)

You really dont know whether the returnValue refers to row 31 or 34.