Multipart formula needed

[Guest]

I have a spreadsheet with two worksheets (A and B).
(A) has information regarding stock ordered and in transit, and (B) is a
complete list, updated weekly, of stock on hand. Both worksheets contain a
column of identifying SKU numbers

I would like to create some kind of formula that will:
Look at the first cell of the column containing the SKU number in (A) and
search the SKU number column for a match in (B)
If/when it finds the identical SKU number in (B), it would find the current
stock on hand qty from that row
Then it would return that value (stock on hand) into an empty cell in
worksheet (A)

Then I could copy and paste this formula down through the whole spreadsheet
- Thanks to anyone who can help!

 

[David McRitchie]

Hi Barb,
As long as the table in sheet B has the SKU number before
the value to be returned you can use VLOOKUP. Worksheet Function
http://www.mvps.org/dmcritchie/excel/vlookup.htm

 

[Guest]

Barb,

Try = SUMIF(Sheet B!$A$2:$A$500 , Sheet1!$A2 , Sheet2!$B$2:$B$500)
and Ctrl+Shift+Enter to execute in a blank cell on sheet A.

Column A in both cases is the SKU number, and column B refers to the stock
on hand list. (It is assumed that the list runs from row 2 to 500 - these can
be altered)

If I'm interpreting your request correctly, this formula will look for the
SKU number in the stock on hand list and match it to the SKU number in the
stock in transit list and show the stock on hand.

This formula avoids having to sort the data into alphanumeric order, and if
the SKU numbers appear more than once in random places in the list on sheet
B, the total for that SKU number will be shown in sheet A. However, the SKU
list in sheet A should only have each SKU number listed once to avoid
duplication.

hope this helps.