A little hard to explain, so look at the attached workbook.
The reference table is in $B$5:$C$15 which list the item in B and each corresponds to a category in C. In $E$5:$E$15 I have a list of the items and each corresponds to a value in the adjacent column (F). In $H$5:$H$7, I list the 3 categories. In I5:I7 I need a formula to look up the first 3 items (if there are 4 items, the 4th should be ignored), lookup the item in the reference table and get the category, and then sum those item. So for Fruit in I5, the answer should be 9 (1 for Pear + 3 for Banana + 5 for pineapple... the formula ignores Peach in E13); I6 should be 18, and I7 should be 23.
I have formulas where I've tried different strategies in each of the I cells, feel free to delete them...
Bookmarks