Lookup with multiple match criteria

[Hurley123]

Hello! First time posting as I am really stuck with where to go with this formula..

I have attached a watered down version of the spreadsheet I'm working on, but essentially what I'm trying to do is "match the entity code with the range and match "TOTAL REVENUE" with the range then do a lookup on the date and bring back the revenue for that entity on that date.

I've left the formula I tried out in cell Sheet 1!E6 however this is bringing back the wrong value.

Thanks in advance!

[Pepe Le Mokko]

Try this in F6 and pull down =SUMPRODUCT((Sheet2!$B$5:$B$16=Sheet1!$C$5)*(Sheet2!$C$5:$C$16=Sheet1!$E$5)*INDEX(Sheet2!$D$5:$H$16,,MATCH(Sheet1!$B6,Sheet2!$D$5:$H$5,0)))

[Hurley123]

It works! Thanks for your help

[Richard Buttrey]

...another way in F6

Please Login or Register  to view this content.

[Pepe Le Mokko]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Richard Buttrey]

...You might also find a Pivot Table a more flexible and useful solution - see attached.

[Hurley123]

Thanks for both your responses!

[Czeslaw]

Another option with the formula

Please Login or Register  to view this content.