How to 6 way lookup using FILTER Function
6-Way Lookup Using the FILTER function
Don't get me wrong—I love the FILTER function and still think it is the ultimate Lookup function in Excel.😎
But.....
When it comes to doing a 6-way lookup...my vote goes to the INDEX/MATCH combo.
FILTER Function👇
=SUM(
FILTER(
FILTER(tblSales,
(tblSales[Item]&tblSales[Buyer]=H3&I3)+
(tblSales[Item]&tblSales[Buyer]=H4&I4)),
(tblSales[#Headers]=H6)+
(tblSales[#Headers]=H7))
)
INDEX/MATCH Combo👇
=SUM(
INDEX( tblSales,
MATCH(H3:H4&I3:I4,tblSales[Item]&tblSales[Buyer],0),
TRANSPOSE(MATCH(H6:H7,tblSales[#Headers],0)))
)
Do you know a better way to do a 6-way lookup?
What do you vote for? FILTER or INDEX/MATCH?
.........
Follow/Connect for more tips & tricks.
..........
#Excel #ExcelTips #CrispExcel #Hr #India #Accounting
