I want to develop a formula that mixes "If" and "Vlookup" functions

**chris.slater** · 03-12-2012, 03:10 PM

I have a requirement to lookup a value in a column, if present, return the number "1" else return the number "0".
Does anyone have any ideas how this can be achieved?
Thanks
Chris

**dilipandey** · 03-12-2012, 03:20 PM

Hi Chris,

Welcome to the forum.
This can be achieved using error handler like If ISError :-

Use the following formula:-

=IF(ISERROR(VLOOKUP(E3,$C$1:$D$50,2,0)),0,1)

where, E3 is the value you want to look for in range C1 to D50. thanks.

regards,
DILIPandey

<click on below 'star' if this helps>

**Bob Phillips** · 03-12-2012, 03:21 PM

Try

=--NOT(ISNA(MATCH(some_value,some_column,0)))

**chris.slater** · 03-13-2012, 07:15 AM

Hi Dilipandey
I have created the following formula based on your reply:
=IF(ISERROR(VLOOKUP(A3,'19 Jul 2011'!A:A,2,0)),0,1)

The value I am looking for is in cell "A3"
The array I want to look in is on worksheet "19 Jul 2011" in column A.
If the value is there I want Excel to return the number "1" or, if not, the number "0".
The above formula is returning "0" for every row even the ones where the value is present.
Could you tell me what the "2,0" after the Column range does? I wonder whether that is the issue.
Thanks
Chris