Vlookup

[tracyblome]

I am so confused. I have been working on this for two days now and can't seem to figure it out. I am working on this for a co-worker.

She has a spreadsheet that has two different columns for product numbers and the quantity for each product number. How can I compare each product number and the quanitity to see if they match? I have approximately 4,000 different rows in each so using VLOOKUP would seem so much easier but I can't seem to use the right formula.

Product #	QTY	Product #	QTY
260005	344	260005	344
260025	2,294	260025	2,294
260026	720	260026	720
260030	220	260030	220
260052	4	260052	4
260053	13	260053	13
260101	2,030	260101	2,030
266001	21	266001	21
266003	44	266003	44
266007	57	266007	57
266015	21	266015	21
270090	15	270090	15
270091	11	270091	11
270098	15	270098	15
270099	99	270099	99
270100	82	270100	82
272057	1,340	272057	1,340
272174	121	272174	121
272218	105	272218	105
272222	68	272222	68
272305	22	272305	22
280001	1,176	280001	1,176
280003	5,040	280003	5,040
280004	9,370	280004	9,370

<COLGROUP><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7350" width=201><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7350" width=201><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY>
</TBODY>

 

[tracyblome]

The totals are wrong and there is a difference of 70- so I just need to see if they match or not.

 

[Audiman]

Ok, This is assuming The first group is in A:B and the second in C:D
In Column E write =IF(B1 = VLOOKUP(A1, C:D, 2,FALSE), "TRUE", "FALSE")
This will return in the column beside the value if its = TRUE, if NOT False..
I'm Also assuming the the Product #'s do not Duplicate.

 

[tracyblome]

Ok, This is assuming The first group is in A:B and the second in C:D
In Column E write =IF(B1 = VLOOKUP(A1, C:D, 2,FALSE), "TRUE", "FALSE")
This will return in the column beside the value if its = TRUE, if NOT False..
I'm Also assuming the the Product #'s do not Duplicate.

No they are not duplicate numbers. The first column came up false....it should be true because they match right?

 

[tracyblome]

There will be matches in Column A and Column C, but that is so I can get a difference in the quantity.

 

[Audiman]

But there are no duplicates in just Column A.. If that is the case. What I said will work.

 

[tracyblome]

No- I have one that is saying false on my product 52003- which is 1226.00 Qty. In both columns that is the same but is saying it is false. There are more product numbers in column C then there is A, does that make a difference?

 

[tracyblome]

521221

0.50

<TBODY>
</TBODY>

521221

0.50

False

<TBODY>
</TBODY>

These were on two different lines. These are Column A, B, C, D, E. It shows false, but they do match so it should be true. I'm wondering if I'm being to confusing....

 

[Audiman]

Actually your not being confusing at all. I can tell from the cells (Im hoping you copied them..) That one is considered a number, and the other a text. Vlookup will not identify them the same even though they look the same. The number 1 is not = "1"? Make sense?

Best bet.. Highlight the Product ID Column, right click on it and select format "text". Then highlight all the values from that column and copy and paste them into a notepad file. In the excel file delete that column. Then go back to the notepad, and CTRL+A and select them all again and put them back. You'll notice now they are all a text now.. Do the same for the other product ID column. It should work after that.

I'm willing to bet your formula you were thinking of was working, just the values were not being identified right. Text vs Number. Text tends to justify to the left, Numbers to the right.

 

[markmzz]

Maybe this (a small modification in Audiman's formula):

Layout

Product #	QTY	Product #	QTY	Result
260005	344	2600055	344	False
260025	2,294	260025	2,294	True
260026	720	260026	720	True
260030	220	260030	220	True
260052	4	260052	4	True
260053	13	260053	13	True
260101	2,03	260101	2,03	True
266001	21	266001	21	True
266003	44	266003	44	True
266007	57	266007	57	True
266015	21	266015	21	True
270090	15	270090	15	True
270091	11	270091	11	True
270098	15	270098	15	True
270099	99	270099	99	True
270100	82	2701001	82	False
272057	1,34	272057	1,34	True
272174	121	272174	121	True
272218	105	272218	105	True
272222	68	272222	68	True
272305	22	272305	22	True
280001	1,176	280001	1,176	True
280003	5,04	280003	5,04	True
280004	9,37	280004	9,37	True
*********	******	*********	******	******

<tbody>
</tbody>

Formula

In E2

=IFERROR(IF(D2&""=VLOOKUP(C2&"",INDEX($A$2:$B$25&"",),2,0),"True","False"),"False")

Markmzz