Vlookup help

J

jswanick

I need help in creating a vlookup formula that will search a single
column that automatically defines the range within the column to
lookup.

On one tab I have a master list of 5000 records for 20 + divisions
reflecting their inventory by SKU. There are multiple instances of the
same SKU number in the column however there is only one instance per
division. I sorted this information by division by SKU. This tab also
includes qty sold and purchased for the past 12 months.

The main tab will have the same SKU's by division number however this
report shows the inventory aging and reserve against obsolete items. I
want to merge the qty sold and purchased in the same row of the
matching division and SKU.

I need the vlookup on the main tab to look at the division number in
the adjacent column including the SKU number, create a range on the
other tab for the same division number, match the SKU, and return the
qty sold information.

Example: tab 1

SKU Number Division Qty Sold Qty Purchased
1223 1 5 10
1334 1 2 12
2244 1 16 5
1223 2 8 20
1334 2 3 6
2244 2 10 5
4477 2 15 8
1223 3 1 10
1334 3 12 24
2244 3 25 32
8974 3 36 18

Example: main
tab Inventory
aging
SKU Number Division Qty Sold Qty Purchased 1-2 years 2-3
years 3-4 years Reserve
1223 1 vlookup vlookup
1 2 $100
1334
1
6 $150
2244 1
1223 2
1334 2
2244 2
4477 2
1223 3
1334 3
2244 3
8974 3
 
T

T. Valko

Try this:

=SUMPRODUCT(--(Sheet2!$A$1:$A$100=$A1),--(Sheet2!$B$1:$B$100=$B1),C1:C100)

Copy across 1 cell then down as needed.
 
T

T. Valko

Ooops!

Need to make the rows in that last array absolute:

=SUMPRODUCT(--(Sheet2!$A$1:$A$100=$A1),--(Sheet2!$B$1:$B$100=$B1),C$1:C$100)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top