There are many different ways - pick one!

EDIT. To give thanks to the forum members who contributed thier time and knowledge. NBVC, DO and DLL

Pikes thread "the best of Excel Forum" has inspired this post - and I will be adding to that one later with suitable extracts from my Excel Folder of magic tricks which I have created from solutions posted on this site........

The other day a poster started a thread with this title "Vlookup or other formula to return data given criteria" it looked like an interesting challenge so I decided to go about investigating a solution and cobbled one together from my aforemention super folder! It worked but i was convinced there was another more efficient way, as such I posted a new thread asking for second opinions - I would like to share the results of this with one and all as (i think its a collection of great formulae) 8 ways to skin a cat!! see the spreadsheet attached for details but here's a taster......... the lookup choose makes me shiver!! brrrrrr

This is a great learning opportunity for any budding excel nerd!

Vlookup with Choose
=VLOOKUP(H2,CHOOSE({1,2},$H$2:$H$13,$D$2:$D$13),2,0)
Vlookup if
=VLOOKUP(H2,IF({0,1},$D$2:$D$13,$H$2:$H$13),2,0)
Lookup
=LOOKUP(2,1/($A$2:$A$13=E2)/($B$2:$B$13=F2)/($C$2:$C$13=$G$1),$D$2:$D$13)
Index match no CSE
=INDEX($D$2:$D$13,MATCH(1,INDEX(($A$2:$A$13=E2)*($B$2:$B$13=F2)*($C$2:$C$13=$G$1),0),0))
Basic Index Match
=INDEX($D$2:$D$13,MATCH(H2,$H$2:$H$13,0))
Sumproduct
=SUMPRODUCT($D$2:$D$13,--($A$2:$A$13=E2),--($B$2:$B$13=F2),--($C$2:$C$13=$G$1))
Sumifs
=SUMIFS($D$2:$D$13,$A$2:$A$13,E2,$B$2:$B$13,F2,$C$2:$C$13,$G$1)
=INDEX($D$2:$D$13,MATCH(1,($A$2:$A$13=E2)*($B$2:$B$13=F2)*($C$2:$C$13=$G$1),0))

Hi Blake 7
i like it