# Vlookup

#### tracyblome

##### New Member
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>

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### tracyblome

##### New Member
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
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
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
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
But there are no duplicates in just Column A.. If that is the case. What I said will work.

#### tracyblome

##### New Member
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
 521221 0.5

<TBODY>
</TBODY>
 521221 0.5 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
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
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

Code:
``````In E2

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

Markmzz

1,170,932
Messages
5,872,779
Members
432,945
Latest member

### 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.

### Which adblocker are you using?

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

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