If Statement for Max value on Index Match Array

[Capt.Ragnar]

I have no other solution than the one posted in the title for the data I'm working with.

Basically, I need a formula that returns a 1 for the true if statment of the Highest number according to each combination of color and vehicle type.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]9
[/TD]
[TD]Red
[/TD]
[TD]Car
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Blue
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Blue
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Blue
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Blue
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Blue
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Blue
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]White
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]White
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]White
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]White
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]White
[/TD]
[TD]Truck
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]White
[/TD]
[TD]Bus
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[gsistek]

Assuming this data is in columns A:C, in D1 try:
=IF(IF(B:B=B1,IF(C:C=C1,A1,0))=9,1,"")
Confirm with CTRL-SHIFT-ENTER, not just enter.

 

[iyyi]

I have no other solution than the one posted in the title for the data I'm working with.

Basically, I need a formula that returns a 1 for the true if statment of the Highest number according to each combination of color and vehicle type.

[TABLE="width: 500"]
<tbody>[TR]
[TD]9[/TD]
[TD]Red[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Blue[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Blue[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Blue[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Blue[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Blue[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]White[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]White[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]White[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]White[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]White[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]White[/TD]
[TD]Bus[/TD]
[/TR]
</tbody>[/TABLE]

Try in D1:
=IF(MAX((B1=B$1:B$13)*(C1=C$1:C$13)*(A$1:A$13))=A1,1,"") Enter with Ctrl+Shift+Enter

 

[Capt.Ragnar]

Nailed it. I was trying to use and index match joining cell values with "&".

Two questions.

1. What is the * actually doing in this formula the way you wrote it and...

2. Why when you try to Copy Paste an array formulat after CSE can the cell being copied not be within the range being pasted into?

You guys are awesome.

Thanks.

 

[iyyi]

Nailed it. I was trying to use and index match joining cell values with "&".

Two questions.

1. What is the * actually doing in this formula the way you wrote it and...

2. Why when you try to Copy Paste an array formulat after CSE can the cell being copied not be within the range being pasted into?

You guys are awesome.

Thanks.

1. The "*" makes sure that all conditions are met simultaneously.
2. I don't understand the question.