Lookup data from one table to another using multiple factors

[wii36o]

Hello,

I am creating a usage projections worksheet and need to correlate data from the customer's usage table to my projections spreadsheet. My spreadsheet contains the item data, and on hand quantity, which I need to compare (ratio) to the usage quantities over a certain period of time which is provided on the customer spreadsheet. I want to do a lookup to pull the quantity used, for a specific period onto my worksheet.

I am not sure how to complete this.

Attached is a workbook with the info. Tab 1 is "Master", and Tab 2 is "Customer Usage".

I need to pull the usage qty (column "d" from "customer usage" tab) into the appropriate column determined by date in the "master" spreadsheet.

If I had to word out the lookup I would say: If Cell A1 (Customer PN) matches from table Master to Customer Usage (Column A), AND the date from Master matches the date in the Customer Usage (column B), then pull the usage quantity (column D in customer usage) back to the Master.

Again the end result is moving the usage quantities from the customer usage spreadsheet to the master worksheet automatically.

Hopefully this makes sense!

Any help would be greatly appreciated!!

multiple lookup test.xlsx

NOTE: I am not sure how to replace the attachment with the correct one... so please note that the spreadsheet attached has values in the Master spreadsheet under the dates. These cells should be blank, and will be filled once a formula pulls this data from the Customer Usage tab. Also the date headers are backwards.. but that shouldn't matter. Sorry.


Thanks,
Matt

[Melvinrobb]

by adding in a helper column combining the CustomerPN and Date, you get a unique lookup value you can use to look up the appropriate Qty.

[wii36o]

Ok. I have done that before and that makes sense... however.... How do I associate both a row header and column header in the lookup?

If I make a joined column, or unique value column, wouldn't it have to exist on both worksheets?


MASTER WORKSHEET

PN 01/01/13 01/05/13 01/09/13 01/13/13

A

B





CUSTOMER WORKSHEET

PN DATE QTY

A 01/01/13 600

A 01/05/13 300

B 01/09/13 500

B 01/13/13 400


Matt

[Melvinrobb]

The formula I put in the attached file shows how you use the row head and column header in the lookup
=IFERROR(VLOOKUP($A2&D$1,'Customer Usage'!$A$2:$E$1167,5,FALSE),"")