Vlookup problem

[Jim Berglund]

I have two worksheets with some matching data. I'm trying to use the following formula to determine if the values in column A on WS1 exist in Column A on WS2.

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:'Equipment Data'!$B$9000,1),"T","F")
I am getting #N/A and #VALUE responses.

What have I done wrong?

Thanks

Jim Berglund

[BenjieLop]

I have two worksheets with some matching data. I'm trying to use the following formula to determine if the values in column A on WS1 exist in Column A on WS2.

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:'Equipment Data'!$B$9000,1),"T","F")
I am getting #N/A and #VALUE responses.

What have I done wrong?

Thanks

Jim Berglund

Try this:

=countif(EquipmentData!$A$2:$A$9000,A2)>0

If A2 (in WS1) exists in Column A in EquipmentData, then the formula will return "TRUE" otherwise, will return "FALSE"

[Jim Berglund]

'Sorry, the formula used was actually

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
"Jim Berglund" <[email protected]> wrote in message news:MYPKd.203110$Xk.124817@pd7tw3no...
I have two worksheets with some matching data. I'm trying to use the following formula to determine if the values in column A on WS1 exist in Column A on WS2.

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
I am getting #N/A and #VALUE responses.

What have I done wrong?

Thanks

Jim Berglund

[Arvi Laanemets]

Hi

Maybe
=IF(ISERROR(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1,0)),"F","T")

(4th parameter set to 0 forces exact match to be searched for)


Arvi Laanemets


"Jim Berglund" <[email protected]> wrote in message
news:1gQKd.203546$8l.164064@pd7tw1no...
'Sorry, the formula used was actually

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
"Jim Berglund" <[email protected]> wrote in message
news:MYPKd.203110$Xk.124817@pd7tw3no...
I have two worksheets with some matching data. I'm trying to use the
following formula to determine if the values in column A on WS1 exist in
Column A on WS2.

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
I am getting #N/A and #VALUE responses.

What have I done wrong?

Thanks

Jim Berglund

[NewsMan]

why don't you try something like this:

=IF(ISERROR(VLOOKUP(A1,'Equipment Data'!$A$2:!$B$9000,1,FALSE)),"",A1)

This will return a blank cell if the numbers don't match or the number
if they do. You can enter this in cell B1 of WS1 and then autofill down.

Jim Berglund wrote:
> 'Sorry, the formula used was actually
>
> =IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
>
> "Jim Berglund" <[email protected] <mailto:[email protected]>>
> wrote in message news:MYPKd.203110$Xk.124817@pd7tw3no...
> I have two worksheets with some matching data. I'm trying to use the
> following formula to determine if the values in column A on
> WS1 exist in Column A on WS2.
>
> =IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
> I am getting #N/A and #VALUE responses.
>
> What have I done wrong?
>
> Thanks
>
> Jim Berglund

[Dave Peterson]

Since you're only checking for existence, you could use =match(), too.

=if(isnumber(match(a2,'equipment data'!$a$2:$b$9000,0)),"T","F")

or even...

=if(countif('equipment data'!$a$2:$b$9000,a2)>0,"t","f")



> Jim Berglund wrote:
>
> I have two worksheets with some matching data. I'm trying to use the following
> formula to determine if the values in column A on WS1 exist in Column A on
> WS2.
>
> =IF(VLOOKUP(A2,'Equipment Data'!$A$2:'Equipment Data'!$B$9000,1),"T","F")
> I am getting #N/A and #VALUE responses.
>
> What have I done wrong?
>
> Thanks
>
> Jim Berglund

--

Dave Peterson

[Harlan Grove]

"Dave Peterson" <[email protected]> wrote...
>Since you're only checking for existence, you could use =match(), too.
>
>=if(isnumber(match(a2,'equipment data'!$a$2:$b$9000,0)),"T","F")

Can't use MATCH against nontrivial 2D ranges. This should be

=IF(ISNUMBER(MATCH(A2,'equipment data'!$A$2:$A$9000,0)),"T","F")

>or even...
>
>=if(countif('equipment data'!$a$2:$b$9000,a2)>0,"t","f")

COUNTIF is better since one COUNTIF call does the work of two other
functions, ISNUMBER and MATCH, but also should only look through col A since
no match for A2 in col A but a match in col B would be a false match.

[Dave Peterson]

Oops, I didn't notice that it was two columns in that range.

But if it were only one column, I think I prefer the =isnumber(match()) better
than =countif(). With lots of data (whatever that means), =countif() seems to
be slower.



Harlan Grove wrote:
>
> "Dave Peterson" <[email protected]> wrote...
> >Since you're only checking for existence, you could use =match(), too.
> >
> >=if(isnumber(match(a2,'equipment data'!$a$2:$b$9000,0)),"T","F")
>
> Can't use MATCH against nontrivial 2D ranges. This should be
>
> =IF(ISNUMBER(MATCH(A2,'equipment data'!$A$2:$A$9000,0)),"T","F")
>
> >or even...
> >
> >=if(countif('equipment data'!$a$2:$b$9000,a2)>0,"t","f")
>
> COUNTIF is better since one COUNTIF call does the work of two other
> functions, ISNUMBER and MATCH, but also should only look through col A since
> no match for A2 in col A but a match in col B would be a false match.

--

Dave Peterson

[Jim Berglund]

Thanks to all of you. I have it working, now.

Jim

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Oops, I didn't notice that it was two columns in that range.
>
> But if it were only one column, I think I prefer the =isnumber(match())
> better
> than =countif(). With lots of data (whatever that means), =countif()
> seems to
> be slower.
>
>
>
> Harlan Grove wrote:
>>
>> "Dave Peterson" <[email protected]> wrote...
>> >Since you're only checking for existence, you could use =match(), too.
>> >
>> >=if(isnumber(match(a2,'equipment data'!$a$2:$b$9000,0)),"T","F")
>>
>> Can't use MATCH against nontrivial 2D ranges. This should be
>>
>> =IF(ISNUMBER(MATCH(A2,'equipment data'!$A$2:$A$9000,0)),"T","F")
>>
>> >or even...
>> >
>> >=if(countif('equipment data'!$a$2:$b$9000,a2)>0,"t","f")
>>
>> COUNTIF is better since one COUNTIF call does the work of two other
>> functions, ISNUMBER and MATCH, but also should only look through col A
>> since
>> no match for A2 in col A but a match in col B would be a false match.
>
> --
>
> Dave Peterson