I have two worksheets, one is 10 x infinite, here is our example to work with
| A | B | C | D | E | F | G | H | I | J |
1 | Name | Dollar Amount | Description | Part Number | Address | States | Country | (Irrelevant Information) | (Irrelevant information) | Quantity |
2 | Bob | $1000.00 | Apples | 10002000 | Route 57 | New York | United States | Red | Lincoln | 1 |
3 | Fred | $3000.00 | Pears | 10002100 | Route 66 | California | Mexico | Blue | Ford | 3 |
4 | George | $2500.00 | Grapes | 10401010 | Route 166 | Arizona | Australia | Gray | Toyota | 5 |
5 | Gerard | $4500.00 | Apricots | 10398392 | Route 87 | Washington | England | Black | Hyundai | 2 |
6 | Brandi | $6500.00 | Strawberries | 18393029 | Route 328 | North Carolina | China | Brown | Chevy | 6 |
7 | Bob | $5000.00 | Pears | 10002100 | Route 166 | Arizona | Australia | Gray | Toyota | 5 |
| CONTINUES for several rows down-->>>VVV | | | | | | | | | |
<tbody>
</tbody>
Note that some information such as price is a multiplier of the quantity, but the location information may change. Also note that the part number determines the description (as any barcode would)
I need to use a VLOOKUP that has a complete list of all part numbers in sequential order (3000+ numbers) with their corresponding descriptions and base price.
The SUMIFS and VLOOKUP needs to check how many of each Bob used, and sum each up.
It would look like this if running a Bob report:
Bob Report | A | B | C | D |
1 | 5000.00 | Pears | 10002100 | 5 |
2 | 1000.00 | Apples | 10002000 | 1 |
<tbody>
</tbody>
And I would run a separate report for each person, Bob, Brandi, Gerard, etc
Most is easy, this big formula is difficult.
Everything about this is easy regarding total pricing (just a simple multiplication, the description is an easy VLOOKUP from the table, but the sumif-vlookup from the other table, with multiple criteria is hard. Criteria if name matches, and part number matches, to then add up cell values.
Thanks again.