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

  • Thread starter Thread starter Jim Berglund
  • Start date Start date
J

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
 
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)


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
 
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)


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)


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
 
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
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)


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)


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
 
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)


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
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)


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)


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
 
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 :-)
----------------------------------------------------------------------------

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
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)


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)


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
 
Morning Bob, Hope the weather over the Purbecks is a little less grey than
Portsmouth :-)
 
Thanks to both of you for the explanation. Some of this stuff is pretty inscrutable...

Jim
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 :-)
----------------------------------------------------------------------------

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
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)


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)


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
 
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.
 
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.
 
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 :-)
----------------------------------------------------------------------------

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.
 
Back
Top