# 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

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.

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)