Vlookup

T

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>
 
Sort by date Sort by votes
Markmzz,

Would that allow the difference between text and numbers? Only asking cause I honestly don't know and I can at least say I learned something today :)
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Audiman said:
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.
Click to expand...


I tried that and still can't get it to work.
 
Upvote 0
Audiman said:
Markmzz,

Would that allow the difference between text and numbers? Only asking cause I honestly don't know and I can at least say I learned something today :)
Click to expand...



Ohhh my goodness- Mr. Excel Wiz! How you figured that out and got that formula I will just have to try to figure it out. I did work. To make this one step harder is there anyway to give the difference in QTY numbers? Let's say the first QTY has 4 on hand, then the next has 42, but the same product number. They don't line up so she would still have to research all of the FALSE statements and try to line it up with each one.
 
Upvote 0
Do the product numbers in A always match what is on the same row in column D (i.e. A2=D2, A3=D3,A4=D4, etc)? If not, try this:

=IF(VLOOKUP(A2,A2:B2,2,FALSE)=VLOOKUP(A2,$C$2:$D$25,2,FALSE),"MATCH","DIFFERENT")

The result will tell you if the product number in column A matches it's like number/quantity somewhere in column D.

Len
 
Upvote 0
tracyblome said:
Ohhh my goodness- Mr. Excel Wiz! How you figured that out and got that formula I will just have to try to figure it out. I did work. To make this one step harder is there anyway to give the difference in QTY numbers? Let's say the first QTY has 4 on hand, then the next has 42, but the same product number. They don't line up so she would still have to research all of the FALSE statements and try to line it up with each one.
Click to expand...

If I understand correctly what you want, maybe this can helps:

Layout

Product #QTYResultProduct #QTYResult
26000534402600053440
2600252,29402600252,2940
2600302200260026720648
260052402600302200
26005313026005240
270100182100260053130
2601012,0302601012,030
266001210266001210
266003440266003440
266007570266007570
266015210266015210
26002672-648270090150
270090150270091110
270091110270098150
270098150270099990
27009999027010082-100
2720571,3402720571,340
27217412102721741210
27221810502722181050
272222680272222680
272305220272305220
2800011,17602800011,1760
2800035,0402800035,040
2800049,3702800049,370
******************************************

<tbody>
</tbody>


Formulas

Code: 
In C2

=IFERROR(B2-VLOOKUP(A2&"",INDEX($D$2:$E$25&"",),2,0),"")

In F2

=IFERROR(E2-VLOOKUP(D2&"",INDEX($A$2:$B$25&"",),2,0),"")

Markmzz
 
Upvote 0
Audiman said:
Markmzz,

Would that allow the difference between text and numbers? Only asking cause I honestly don't know and I can at least say I learned something today :)
Click to expand...

This part of the formula

VLOOKUP(C2&"",INDEX($A$2:$B$25&"",),2,0)

C2&"" - text

$A$2:$B$25&"" - text too

will work only with text.

Markmzz
 
Upvote 0
No- It comes up with the wrong number. There are over 3,000 rows worth of data and it is not showing. It is only coming up with about 20 off when it should be 70. Now it is just trying to find where that 70 is off.
 
Upvote 0
tracyblome said:
No- It comes up with the wrong number. There are over 3,000 rows worth of data and it is not showing. It is only coming up with about 20 off when it should be 70. Now it is just trying to find where that 70 is off.
Click to expand...

Could you post the formulas that you are using?

And a small example with data (with problem)?

Markmzz
 
Upvote 0
markmzz said:
Could you post the formulas that you are using?

And a small example with data (with problem)?

Markmzz
Click to expand...

When I add all these up it doesn't come up to the 70 that I'm off.

31210140.0830821820
31222824030821930
312234377030822010
31223652030822270
3203926440.2530822320
320396230308224600
32040012503082270.90
3205221465030900010
32201817503120021670
3220208810312004600
32202212603120156440
32202321-0.373120173910
32202436803120181180
32202544803121013.92-0.08
32202615400312228240
33003419703122343770
330035280312236520
33003632390.5320392643.75-0.25
33004226480320396230
33004310003204001250
330044942032052214650
330049603220181750
33006045703220208810
33006623103220221260
330067104032202321.370.37
33006817.2503220243680
330070680.53220254480
330071285032202615400
330072278703300341970
3300731670.5330035280
330076503300363238.5-0.5
330078110.533004226480
330079603300431000
330080203300449420
33008129033004960
3300847403300604570
3323404503300662310

<COLGROUP><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7350" width=201><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7350" width=201><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>
 
Upvote 0
Sorry, but I didn't understand.

The formulas are working.

Look at the part numbers:

312101
320392
322023
330036

Could you post the formulas that you're using?

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,827
Messages
6,121,809
Members
449,048
Latest member
greyangel23

Share this page

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
Back
Top