vlookup

[matt]

I have checked tech on the net.com to solve a problem but
their formula does not seem to work. I have 20 rows I am
working with and two seperate columns. I am seeing if the
numbers in the cells in one column are the same as in the
second column. When I tried to execute the formula, Tech
on the net's formula only shows if there is a duplicate on
row 1 but not on rows 2-20. Below is the scenario I am
doing. Please let me know what have I missed? I want to
show if duplicates exist on all 20 rows. Thanks! (here
is the scenario)

Question: I have a list of #s in column A (lets say 1-
20). There is a master list in another column that may
not include some of the column A #s. I want a formula in
column B to say (if A1 exists in the master list,
then "Yes", "No". Is this possible?

Answer: This can be done with a formula that utilizes a
combination of the VLookup function, IF function, and ISNA
function.



Based on the spreadsheet above:

=IF(ISNA(VLOOKUP(A2,D2188,1,FALSE)),"No","Yes") would
return "No"
=IF(ISNA(VLOOKUP(A5,D5191,1,FALSE)),"No","Yes") would
return "Yes"

First, you need to enter a FALSE in the last parameter of
the VLookup function. This will ensure that the VLookup
will test for an exact match.

If the VLookup function does not find an exact match, it
will return the #N/A error. By using the IF and ISNA
functions, you can return a "Yes" value if an exact match
is found. Otherwise, a "No" value is returned.

 

[Patti]

Hi Matt,

I tried this formula, and it worked for me:

=IF(ISNA(VLOOKUP(A1,$G$1:$G$20,1,FALSE)),"No","Yes")

I copied it down A1 through A20 and it showed "Yes"
or "No" for duplicates in G1 through G20. Is that what
you're trying to do?

Patti

 

[Matt]

Hi Patti,

Yes, and I do not understand how to copy it down from a1
through a20. This is what has not been happening
thusfar. I can usually copy things in Excel without a
problem. How did you do it?

 

[Patti]

Hi Matt,

Click on cell A1, hit CTRL-C, hilight cells A2 through A20
and hit CTRL-V. Do you have the "$" in the formula?

Patti

 

[Guest]

Patti

How did you copy it down a1 through a20? This has been
my stumbling block. Thanks!

 

[Patti]

If the data is in cells A1 through A20, for example,
you'll enter the formula in cell B1. Make sure that the
A1 in the formula doesn't have the "$" around it but that
the master range (the $G$1:$G$20) does. Then click in
cell B1, move the curser to the bottom right corner until
it becomes a thin cross and then double click. The
formula will auto fill in cells B1 through B20.

 

[Guest]

Patti or anyone else

Specifically how did you copy it down a1 through 20? I
am a novice and this has been my stumpling block.
Thanks!

 

[Patti]

The data that you're comparing is in columns A and G.
You're entering the formula in cell B1 and copying it down
to B20. The formula in B1 is:

=IF(ISNA(VLOOKUP(A1,$G$1:$G$20,1,FALSE)),"No","Yes")

Make sure that the A1 in the formula doesn't have the "$"
around it but that the master range (the $G$1:$G$20)
does. Then click in cell B1, move the curser to the
bottom right corner until it becomes a thin cross and then
double click. The formula will auto fill in cells B2
through B20.