lookups uising 2 workbooks and multiple criteria

[aaron83]

Hi everyone,

Need urgent help doing lookups across to workbooks for multiple criteria - normally i do vlookups but obviously there is a limitation to more than one source item.

In book 1 (Roll Data) i have a Purchase Order Number and a Product ID. I need to then find the date received which is help in workbook 2, which is based on a certain date range to determine if I received the goods in said period or they were still in transit

In book 2 i have the same information but with the date included (Purchase Order Number is noted here as PO Number, and Product ID is called Item Number)

So in effect i am trying to use workbook 1 as the final data point and pull the date received from book 2 using the PO number then the part number

Appreciate help here folks
Roll Data - date received 1.xlsx

[FDibbins]

Try this ARRAY formula for Date Received...
=INDEX([receipts.xlsx]Sheet1!$R:$R,MATCH(E4&H4,[receipts.xlsx]Sheet1!$B:$B&[receipts.xlsx]Sheet1!$I:$I,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Im not sure which columns contain the other info, but for them, just change the INDEX reference

[aaron83]

Try this ARRAY formula for Date Received...
=INDEX([receipts.xlsx]Sheet1!$R:$R,MATCH(E4&H4,[receipts.xlsx]Sheet1!$B:$B&[receipts.xlsx]Sheet1!$I:$I,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Im not sure which columns contain the other info, but for them, just change the INDEX reference

Thanks for the information - i have tried this and it does work for some portions. Sadly a lot of dates are returning as 0-Jan-1900 even though i can locate them in the receipts book. Any thoughts as to why i can manually locate them but not with the formula?