If statement question?

[Anti-thesis]

I have 3 different figures in different cells (A2, B2, C2). How do I write a an if statement (assuming thats the best way to do this) that will pick the whichever cell has the highest value? Ideally I'd like it to return the text above whichever cell is highest (for example, "Project 1" is in cell A1, Project 2 is in B1 and Project 3 is in B3).

Thanks in advance.

 

[Seti]

Try this, assuming no ties:

=INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0))

 

[Anti-thesis]

Thanks, that works great. I do have one other question though. Im not sure what that formula says (in layman terms) when its broken down because I havent used index/match functions in the past.

 

[Seti]

Ok.

MAX(A2:C2) finds the largest value in the range A2:C2

MATCH(MAX(A2:C2),A2:C2,0) then finds the location in the range A2:C2 of the max value. The last 0 forces an exact match. If you have duplicate highest values, this will find the 1st only

INDEX(A1:C1,n) then takes the range A1:C1 and selects the nth element of the range.

So if you have the max value in B2, then the Max function will return the value in B2. The Match function will return a 2 since the location of the value in B2 is in the 2nd element of the range. The Index function will return the 2nd element of that range, hence B1.

Hope this helps.

 

[Anti-thesis]

so if the formula is in line 52 instead of line 2 it would return a 52 since the value in b52 is the 52nd element and the index func will return the 52 element of that range hence b51? is that right or am i going about this wrong?

 

[Seti]

Here is a shot of the data on row 51/52:

Book3
	A	B	C	D
51	Prod 1	Prod 2	Prod 3
52	22	154	66
53
54
55
56	Prod 2
Sheet1

Since B52 has the greatest value, we still return a 2 from the Match function, the 2nd element in the range A52:C52. This then indexes into A51:C51 and pulls the 2nd element, B51 which has a value of Prod 2.

 

[Anti-thesis]

thanks again as always