What function to use? VLOOKUP?

[karatelovr]

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

 

[swatsp0p]

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

 

[Bernie Deitrick]

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]

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 response
("DISTY","NOT DISTY") to meet your needs. Copy this formula down you
range of data.

HTH

Bruce

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

 

[swatsp0p]

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

 

[karatelovr]

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

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

 

[Ron Rosenfeld]

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

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

and copy/drag down to C317



--ron