If statement question?

Anti-thesis

New Member
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).

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try this, assuming no ties:

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

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.

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.

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?

Here is a shot of the data on row 51/52:
Book3
ABCD
51Prod 1Prod 2Prod 3
522215466
53
54
55
56Prod 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.

thanks again as always

Replies
7
Views
300
Replies
2
Views
146
Replies
2
Views
157
Replies
1
Views
181
Replies
4
Views
211

1,203,665
Messages
6,056,638
Members
444,879
Latest member
suzndush

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back