Vlookup

tracyblome

New Member
Joined
Jan 6, 2014
Messages
11
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. :confused:

Product #QTYProduct #QTY
260005344 260005344
2600252,294 2600252,294
260026720 260026720
260030220 260030220
2600524 2600524
26005313 26005313
2601012,030 2601012,030
26600121 26600121
26600344 26600344
26600757 26600757
26601521 26601521
27009015 27009015
27009111 27009111
27009815 27009815
27009999 27009999
27010082 27010082
2720571,340 2720571,340
272174121 272174121
272218105 272218105
27222268 27222268
27230522 27230522
2800011,176 2800011,176
2800035,040 2800035,040
2800049,370 2800049,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>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

tracyblome

New Member
Joined
Jan 6, 2014
Messages
11
The totals are wrong and there is a difference of 70- so I just need to see if they match or not.
 

Audiman

New Member
Joined
Sep 10, 2009
Messages
31
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.
 
Last edited:

tracyblome

New Member
Joined
Jan 6, 2014
Messages
11
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

New Member
Joined
Jan 6, 2014
Messages
11

ADVERTISEMENT

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

Audiman

New Member
Joined
Sep 10, 2009
Messages
31
But there are no duplicates in just Column A.. If that is the case. What I said will work.
 

tracyblome

New Member
Joined
Jan 6, 2014
Messages
11

ADVERTISEMENT

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

New Member
Joined
Jan 6, 2014
Messages
11
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

New Member
Joined
Sep 10, 2009
Messages
31
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.
 
Last edited:

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Maybe this (a small modification in Audiman's formula):

Layout

Product #QTYProduct #QTYResult
2600053442600055344False
2600252,2942600252,294True
260026720260026720True
260030220260030220True
26005242600524True
2600531326005313True
2601012,032601012,03True
2660012126600121True
2660034426600344True
2660075726600757True
2660152126601521True
2700901527009015True
2700911127009111True
2700981527009815True
2700999927009999True
27010082270100182False
2720571,342720571,34True
272174121272174121True
272218105272218105True
2722226827222268True
2723052227230522True
2800011,1762800011,176True
2800035,042800035,04True
2800049,372800049,37True
************************************

<tbody>
</tbody>


Formula

Code:
In E2

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

Markmzz
 

Forum statistics

Threads
1,141,715
Messages
5,708,049
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top