Mapping value if it matches criteria - Excel

Question

I have a column(Shopping Cart) that I am looking to populate. The data that I am looking to populate this cell with has criteria(Grocery List) that, if met, should equal another criteria via a mapping tool. The information is Maybe an example will better explain,

Shopping Cart - Sheet1

Fruit   Price  
Apples    0  
Bananas   0  
Oranges   0

Grocery List - Sheet2 which changes daily

*Grocery List*  *Price*
Eggs             3.00  
Granny Smith     .50
Bread            1.00
Mandarin         .65 
Bacon            2.50
Clementine       .60
Red Delicious    .70

Here is where I am getting confused.

Mapping Table. ie, so regardless of the type of Apple, my shopping cart only registers Apple - Sheet 3

*Food*    *Type*        
Apple     Macintosh  
Apple     Granny Smith  
Apple     Red Delicious  
Orange    Clementine  
Orange    Mandarin  
Orange    Florida

I want my shopping cart to be updated with the most expensive fruit in each group. After my first stop at the grocery store, this is what my refrigerator would look like,

Shopping Cart - Sheet1

Fruit   Price  
Apple    .70  
Banana    0  
Orange   .65

So in Excel terms, I need the MAX of a group (assuming INDEX MATCH) of cells that meet a certain criteria (assuming IF). I import a spreadsheet (Grocery List) into a certain tab which I would pull that information from. What I am getting tripped up is with the mapping part. I feel like I am missing something basic here and would appreciate if someone could lead me in the right direction.

Let me know if any more information is needed to make this clearer.

Answer 1

Use this array formula, I put mine on all the same sheet:

=MAX(IF(ISNUMBER(MATCH($E$2:$E$8,IF($H$2:$H$7=A2,$I$2:$I$7),0)),$F$2:$F$8))

Being an array formula it needs to be confirmed with ctrl-shift-enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula:

I will caution though, every text must be an EXACT match or this will not work.