Finding Matching Values in Separate Sheets and Copying Accompanying Data

**hamidxa** · 05-24-2012, 06:27 PM

So, I'm working with 2 different reports.

Sheet 1, Column A has product models numbers
Sheet 2, Column A, has most of the exact same model numbers

Sheet 1, Column B has a QTY count
Sheet 2, Column B does not have a QTY count

I would like to:
Copy the QTY count from Sheet 1 Column B
to Sheet 2 Column B as long as it corresponds to the same model number

So for example:
If Sheet 1 Column A contains the model number "32LK" in cell A6
And Sheet 1 Column B contains the QTY 15 in cell B6

And Sheet 2 Column A contains the model number "32LK" in cell A14
Then I would like the QTY of 15 copied into Sheet 2 cell B14

This will need to check for all matching model numbers in both columns in both sheets and copy all applicable QTY values accordingly as stated above.

Really struggling to find a solution here, so help would be EXTREMELY appreciated!

Thanks in advance

**erok** · 05-24-2012, 06:40 PM

This is all you need to do; paste this in sheet 2 column B:

Please Login or Register  to view this content.

What this does is looks up the value in Sheet2 A1 in Sheet1 column A and spits out the value in Sheet1 column B that corresponds to it. You could also use the INDEX or VLOOKUP functions.

**hamidxa** · 05-24-2012, 06:50 PM

Hi Erok,

I tried that, but the values that I'm now seeing in Sheet 2 Column B look like gibberish.
Sheet 2 Column B is populated with values now, but every other value is a 0 (when I don't have any 0 QTY's), some are negative (when no negative qty's should exist), and the remaining values are all duplicates of one another (for example, qty of 694 and 730 appear repeatedly).

I was supposed to copy/paste that equation in Sheet 2 Cell B1, and then drag down across Column B correct?

Please see attached image for an exmaple:
s2wc.JPG

**hamidxa** · 05-24-2012, 06:53 PM

S1.JPG Sheet 1: Column A = Model # , Column B = QTY
S2.JPG Sheet 2: Column A = Model # , Column B = where I would like to have qty's appear for matching model #s

As can be seen, I've reverted back to a blank column B in Sheet 2.
The attached images are basically what my 2 sheets look like at the moment without any code/equations in them.

**arlu1201** · 05-25-2012, 02:58 AM

Hamidxa,

You have created 2 more duplicate threads in addition to this one. This is against the rules. I have closed the other 2. Please continue in this thread.

**TMS** · 05-25-2012, 03:05 AM

@hamidxa: and, to highlight the problem, you have had different answers from different people in each of the threads. So, have a look at the other threads and, in future, please don't waste everyone's time. It is far better if we are all aware of the solutions posed.

@arlette: thanks for putting the brake on.

**Deepal Udeshi** · 05-25-2012, 03:56 AM

Hi,

I tried this formula and its working fine. Incase the Sheet2 has values that are not found in Sheet 1 it returns incorrect results.
Secondly when you paste the formula and drag it to the below rows, just check if the formula is automatically modified correctly. Only the loopup value will change for each row.
Thanks
Deepal