How do I determine if a value in one column exists in another column?

[Jim Berglund]

I tried

=IF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"),
but it produces "T" for all values - even if they do not exist.

Thanks in advance,
Jim

[Bob Phillips]

Jim,

It is bound to exist. If you test whether the value in A2 is in any cell within column A, it will always succeed as A2 is part of column A.

You need to put your match value elsewhere, or change the range to A3:A1000 say.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Berglund" <[email protected]> wrote in message news:YoQKd.203570$8l.161578@pd7tw1no...
I tried

=IF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"),
but it produces "T" for all values - even if they do not exist.

Thanks in advance,
Jim

[Bob Phillips]

Oops, just noticed that it is not necessarily true unless the sheet is 'Equipment Data'

Try this version instead

=IF(ISNUMBER(MATCH(A2,'Equipment Data'!A:A,0)),"T","F")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Jim,

It is bound to exist. If you test whether the value in A2 is in any cell within column A, it will always succeed as A2 is part of column A.

You need to put your match value elsewhere, or change the range to A3:A1000 say.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Berglund" <[email protected]> wrote in message news:YoQKd.203570$8l.161578@pd7tw1no...
I tried

=IF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"),
but it produces "T" for all values - even if they do not exist.

Thanks in advance,
Jim

[Jim Berglund]

Yes, the values are not in the same worksheet, but the values are alphanumeric.

However, I tried your suggestion and it works perfectly, now...
Why is the ISNUMBER required, and why does it work with Alphanumeric values, please?

Jim
"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Oops, just noticed that it is not necessarily true unless the sheet is 'Equipment Data'

Try this version instead

=IF(ISNUMBER(MATCH(A2,'Equipment Data'!A:A,0)),"T","F")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Jim,

It is bound to exist. If you test whether the value in A2 is in any cell within column A, it will always succeed as A2 is part of column A.

You need to put your match value elsewhere, or change the range to A3:A1000 say.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Berglund" <[email protected]> wrote in message news:YoQKd.203570$8l.161578@pd7tw1no...
I tried

=IF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"),
but it produces "T" for all values - even if they do not exist.

Thanks in advance,
Jim

[Bob Phillips]

HI Jim,

Sorry about the false trail :-)

The ISNUMBER is required because MATCH will return a number if it matches, regardless of a text or numeric match, but will error if no match. Therefore, by adding the ISNUMBER test, you get a True for a match, and a False for no match, thereby feeding the IF.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Berglund" <[email protected]> wrote in message news:Rg%Kd.213065$6l.113022@pd7tw2no...
Yes, the values are not in the same worksheet, but the values are alphanumeric.

However, I tried your suggestion and it works perfectly, now...
Why is the ISNUMBER required, and why does it work with Alphanumeric values, please?

Jim
"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Oops, just noticed that it is not necessarily true unless the sheet is 'Equipment Data'

Try this version instead

=IF(ISNUMBER(MATCH(A2,'Equipment Data'!A:A,0)),"T","F")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Jim,

It is bound to exist. If you test whether the value in A2 is in any cell within column A, it will always succeed as A2 is part of column A.

You need to put your match value elsewhere, or change the range to A3:A1000 say.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Berglund" <[email protected]> wrote in message news:YoQKd.203570$8l.161578@pd7tw1no...
I tried

=IF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"),
but it produces "T" for all values - even if they do not exist.

Thanks in advance,
Jim

[Ken Wright]

It's not the alphanumeric that's doing it, but rather what the MATCH function does vs what the IF function expects by way of an argument. The syntax for the IF function is

=IF(CONDITION , If_TRUE_Do_This , If_FALSE_Do_This)

Therefore for the result of the CONDITION, all that the IF function is expecting is a TRUE or FALSE. By using MATCH, you are passing a value that could be anything from 1 to 65536. The only numeric values that the function can handle because it is expecting TRUE/FALSE is 1 or 0 because in Excel TRUE = 1 and FALSE = 0. Any other numbers of 2,3,4, etc are meaningless as far as IF is concerned. What you do therefore is simply use ISNUMBER, which looks at what the MATCH function returns as a result and if it returns a number because there is a match then just the fact that it is a number means it gives TRUE, and if there is no match then there is no number returned and so it gives FALSE. The result of this is then passed to the IF function and off it goes.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Jim Berglund" <[email protected]> wrote in message news:Rg%Kd.213065$6l.113022@pd7tw2no...
Yes, the values are not in the same worksheet, but the values are alphanumeric.

However, I tried your suggestion and it works perfectly, now...
Why is the ISNUMBER required, and why does it work with Alphanumeric values, please?

Jim
"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Oops, just noticed that it is not necessarily true unless the sheet is 'Equipment Data'

Try this version instead

=IF(ISNUMBER(MATCH(A2,'Equipment Data'!A:A,0)),"T","F")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Jim,

It is bound to exist. If you test whether the value in A2 is in any cell within column A, it will always succeed as A2 is part of column A.

You need to put your match value elsewhere, or change the range to A3:A1000 say.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Berglund" <[email protected]> wrote in message news:YoQKd.203570$8l.161578@pd7tw1no...
I tried

=IF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"),
but it produces "T" for all values - even if they do not exist.

Thanks in advance,
Jim

[Ken Wright]

Morning Bob, Hope the weather over the Purbecks is a little less grey than
Portsmouth :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

[Bob Phillips]

Cloudy but bright here Ken.

Bob


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Morning Bob, Hope the weather over the Purbecks is a little less grey than
> Portsmouth :-)
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>

[Jim Berglund]

Thanks to both of you for the explanation. Some of this stuff is pretty inscrutable...

Jim
"Ken Wright" <[email protected]> wrote in message news:[email protected]...
It's not the alphanumeric that's doing it, but rather what the MATCH function does vs what the IF function expects by way of an argument. The syntax for the IF function is

=IF(CONDITION , If_TRUE_Do_This , If_FALSE_Do_This)

Therefore for the result of the CONDITION, all that the IF function is expecting is a TRUE or FALSE. By using MATCH, you are passing a value that could be anything from 1 to 65536. The only numeric values that the function can handle because it is expecting TRUE/FALSE is 1 or 0 because in Excel TRUE = 1 and FALSE = 0. Any other numbers of 2,3,4, etc are meaningless as far as IF is concerned. What you do therefore is simply use ISNUMBER, which looks at what the MATCH function returns as a result and if it returns a number because there is a match then just the fact that it is a number means it gives TRUE, and if there is no match then there is no number returned and so it gives FALSE. The result of this is then passed to the IF function and off it goes.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Jim Berglund" <[email protected]> wrote in message news:Rg%Kd.213065$6l.113022@pd7tw2no...
Yes, the values are not in the same worksheet, but the values are alphanumeric.

However, I tried your suggestion and it works perfectly, now...
Why is the ISNUMBER required, and why does it work with Alphanumeric values, please?

Jim
"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Oops, just noticed that it is not necessarily true unless the sheet is 'Equipment Data'

Try this version instead

=IF(ISNUMBER(MATCH(A2,'Equipment Data'!A:A,0)),"T","F")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Jim,

It is bound to exist. If you test whether the value in A2 is in any cell within column A, it will always succeed as A2 is part of column A.

You need to put your match value elsewhere, or change the range to A3:A1000 say.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Berglund" <[email protected]> wrote in message news:YoQKd.203570$8l.161578@pd7tw1no...
I tried

=IF(MATCH(A2,'Equipment Data'!A:A,0),"T","F"),
but it produces "T" for all values - even if they do not exist.

Thanks in advance,
Jim

[Bob Phillips]

"Jim Berglund" <[email protected]> wrote in message news:Oa9Ld.221477$6l.43827@pd7tw2no...
Thanks to both of you for the explanation. Some of this stuff is pretty inscrutable...

Not really Jim, it is just trial and error, practice and experience. Look out for responses by some of the formula masters like Aladin Akyurek, Peo Sjoblom, Jason Morin, Daniel M, and Domenic, and especially (if you can understand them :-)), Harlan Grove (the master of esoteric, but great, formulae), and you'll soon get the hang of it.

[Bob Phillips]

"Ken Wright" <[email protected]> wrote in message news:[email protected]...
The only numeric values that the function can handle because it is expecting TRUE/FALSE is 1 or 0 because in Excel TRUE = 1 and FALSE = 0. Any other numbers of 2,3,4, etc are meaningless as far as IF is concerned. What you do therefore is simply use ISNUMBER, which looks at what the MATCH function returns as a result and if it returns a number because there is a match then just the fact that it is a number means it gives TRUE, and if there is no match then there is no number returned and so it gives FALSE. The result of this is then passed to the IF function and off it goes.

Not quite true Ken. IF handles any non-zero value as TRUE. For instance
=IF(MATCH("a",{"b","a","c"},0),"Y","N")
will return Y even though the match returns an index of 2. If you can guarantee that the value is always in the list, you don't need ISNUMBER, that is to trap the non-match conditions.

[Ken Wright]

My apologies, you are absolutely correct Bob. Should have tried it first, but i was convinced...... :-(

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Bob Phillips" <[email protected]> wrote in message news:%[email protected]...
"Ken Wright" <[email protected]> wrote in message news:[email protected]...
The only numeric values that the function can handle because it is expecting TRUE/FALSE is 1 or 0 because in Excel TRUE = 1 and FALSE = 0. Any other numbers of 2,3,4, etc are meaningless as far as IF is concerned. What you do therefore is simply use ISNUMBER, which looks at what the MATCH function returns as a result and if it returns a number because there is a match then just the fact that it is a number means it gives TRUE, and if there is no match then there is no number returned and so it gives FALSE. The result of this is then passed to the IF function and off it goes.

Not quite true Ken. IF handles any non-zero value as TRUE. For instance
=IF(MATCH("a",{"b","a","c"},0),"Y","N")
will return Y even though the match returns an index of 2. If you can guarantee that the value is always in the list, you don't need ISNUMBER, that is to trap the non-match conditions.

[corine]

Hi Jim,

Try this.

=IF(A2=VLOOKUP(A2,$D$1:$D$17,1,FALSE),"T")

I've used the function IF and VLOOKUP. It looks up whether the value in column A exists in column D (range D1:D17). If it finds the value in column D it will return "T" (condition if TRUE) and if false it will then return the "N/A" message which means it dosen't find the value in column D.

Corine

[BenjieLop]

<< Not really Jim, it is just trial and error, practice and experience. Look out for responses by some of the formula masters like Aladin Akyurek, Peo Sjoblom, Jason Morin, Daniel M, and Domenic, and especially (if you can understand them :-)), Harlan Grove (the master of esoteric, but great, formulae), and you'll soon get the hang of it.>>

I would include JulieD in this list of formula masters ...