Look ups with multiple criteria and multiple tabs
I am an okay user of excel but I am trying to make a big impression on my boss. I am interning now and think I could solidify this into full time employment by pulling this off.
I have 4 data tabs, each tab has 5 columns (and a ridiculous amount or rows), 4 are the descriptors and the 5th is the information I want back.
On the result tab, I have 3 entry points and one fixed data point. I would like to be able to change the 3 entry points to produce a lookup from the data tabs.
Right now I am using SUMPRODUCT and IF but can only get it to go through 2 tabs.=IF(SUMPRODUCT((FR!A2:A18334=Sheet4!B2)*(FR!B2:B18334=Sheet4!C2)*(FR!C2:C18334=Sheet4!D2)*(FR!D2:D18334=Sheet4!A4)*(FR!E2:E18334))=0,(SUMPRODUCT((DE!A2:A35000=Sheet4!B2)*(DE!B2:B35000=Sheet4!C2)*(DE!C2:C35000=Sheet4!D2)*(DE!D2:D35000=Sheet4!A4)*(DE!E2:E35000))),(SUMPRODUCT((FR!A2:A18334=Sheet4!B2)*(FR!B2:B18334=Sheet4!C2)*(FR!C2:C18334=Sheet4!D2)*(FR!D2:D18334=Sheet4!A4)*(FR!E2:E18334))))
There has to be an easier more efficent way to do this. How do I make this work?
DEPART ARRIVE PALLET
fr01 FI01 20 <--changeable entry points
A € 40.00 <--(A is the client) the 40.00 is the return
Data tab (4 of these, with the possibility for expansion)
FR01 FI01 1 a € 2.00
FR01 FI01 1 b € 4.00
FR01 FI01 1 c € 6.00
FR01 FI01 1 d € 8.00
FR01 FI01 1 e € 10.00
FR01 FI02 1 a € 12.00
FR01 FI02 1 b € 14.00
FR01 FI02 1 c € 16.00
FR01 FI02 1 d € 18.00
FR01 FI02 1 e € 20.00
any push in the right direction would be greatly appreciated.
Thanks in advance,