What function to use? VLOOKUP?
 

::Here is what I am trying to do:

I have a list of names, some names are customers, some names are
distributors. I want to know who is a disty and who is a customer.
So, next to this column of names, I have a column of Distys only. I
want to match the names in column 1 to a matching name in column 2. If
no match exists (i.e., the name is that of a customer, NOT a disty), I
want to result in either a blank field or some other way of knowing
there was no match.

This is the formula I tried and it didn't work:
*=VLOOKUP(A2;$A$2:$C$317;2;FALSE) * where the 3rd colum is where I
would see the match. However, as written, all the formula does is copy
whatever is in column 2 into column 3 (even if the names do not match).

TIA::

:confused:


--
karatelovr
------------------------------------------------------------------------
karatelovr's Profile: http://www.excelforum.com/member.php...o&userid=27413
View this thread: http://www.excelforum.com/showthread...hreadid=469252

First, I would insert a blank column between A and B.

In B1 I would enter this formula:

=IF(ISNA(VLOOKUP(A1,$C$1:$C$100,1,0))=TRUE,"NOT DISTY","DISTY")

Of course, adjust the ranges (A1,$C$1:$C$100) and responses
("DISTY","NOT DISTY") to meet your needs. Copy this formula down your
range of data.

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469252

In C2, try:

=IF(ISERROR(MATCH(A2;$B$2:$B$317;FALSE));"Customer ";"Distributor")

and copy down to C317.

HTH,
Bernie
MS Excel MVP


"karatelovr" wrote in message
...

::Here is what I am trying to do:

I have a list of names, some names are customers, some names are
distributors. I want to know who is a disty and who is a customer.
So, next to this column of names, I have a column of Distys only. I
want to match the names in column 1 to a matching name in column 2. If
no match exists (i.e., the name is that of a customer, NOT a disty), I
want to result in either a blank field or some other way of knowing
there was no match.

This is the formula I tried and it didn't work:
*=VLOOKUP(A2;$A$2:$C$317;2;FALSE) * where the 3rd colum is where I
would see the match. However, as written, all the formula does is copy
whatever is in column 2 into column 3 (even if the names do not match).

TIA::

:confused:


--
karatelovr
------------------------------------------------------------------------
karatelovr's Profile: http://www.excelforum.com/member.php...o&userid=27413
View this thread: http://www.excelforum.com/showthread...hreadid=469252

swatsp0p Wrote:
I would enter this formula:

=IF(ISNA(VLOOKUP(A1,$C$1:$C$100,1,0))=TRUE,"NOT DISTY","DISTY")

Of course, adjust the ranges (A1,$C$1:$C$100) and responses
("DISTY","NOT DISTY") to meet your needs. Copy this formula down your
range of data.

HTH

Bruce
::
I used the above formula (FWIW I have to use semicolons instead of
commas) but the results are all \"DISTY\". :( Also, what is the
zero for in the VLOOKUP formula?::


--
karatelovr
------------------------------------------------------------------------
karatelovr's Profile: http://www.excelforum.com/member.php...o&userid=27413
View this thread: http://www.excelforum.com/showthread...hreadid=469252

karatelovr Wrote:
::
I used the above formula (FWIW I have to use semicolons instead of
commas) but the results are all \"DISTY\". :( Also, what is the
zero for in the VLOOKUP formula?::

I am guessing the data in your "DISTRIBUTOR" list do NOT really match
the data in your ALL NAMES list, therefore all matches fail. Verify
spelling, spaces and punctuation.

The zero in the formula is the same as FALSE (btw, 1=TRUE) and forces
the lookup to find an exact match or return the #N/A error. Bernie
used the same philosophy in his MATCH formula. Both should find
matches and return the desired results.


Good Luck.

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469252

aHHH, so both formulas (VLOOKUP and MATCH) are case sensitive? I tried
the MATCH formula and it returned only "CUSTOMER" as the result. My
column of All Names is in Caps where as my column of distys are Title
Case.

I do realize I will get some error whenever a dash or something else is
added on to one name and not the other - wish I could get it to accept
wildcards, but then we're talking writing a program and at this point,
it's fast to do this manually!


--
karatelovr
------------------------------------------------------------------------
karatelovr's Profile: http://www.excelforum.com/member.php...o&userid=27413
View this thread: http://www.excelforum.com/showthread...hreadid=469252

On Tue, 20 Sep 2005 12:32:22 -0500, karatelovr
wrote:


::Here is what I am trying to do:

I have a list of names, some names are customers, some names are
distributors. I want to know who is a disty and who is a customer.
So, next to this column of names, I have a column of Distys only. I
want to match the names in column 1 to a matching name in column 2. If
no match exists (i.e., the name is that of a customer, NOT a disty), I
want to result in either a blank field or some other way of knowing
there was no match.

This is the formula I tried and it didn't work:
*=VLOOKUP(A2;$A$2:$C$317;2;FALSE) * where the 3rd colum is where I
would see the match. However, as written, all the formula does is copy
whatever is in column 2 into column 3 (even if the names do not match).

TIA::

:confused:


In C2:
=IF(COUNTIF($A$2:$A$317,B2)0,"Disty","")

and copy/drag down to C317



--ron