If - Then Statements

[Lou]

I am trying to compare data in 12 columns and have either the cell
address or a value associated with a cell (1-12) that has the highest
value returned to the 13th column. The 'max' command won't work
because it returns the actual max value. I don't want the value, only
the cell name or (preferably) a number referring to the cell (1-12)
returned.

I have considered using HLOOKUP but don't think that will work as I
cannot keep resorting the data in ascending order. My order needs to
stay the same.

I've tried the IF function, but it only works with 7 ifs. Here is a
sample of the formula that I am using.

=IF(D193=MAX(D193:O193),1,IF(E193=MAX(E193:O193),2,IF(F193=MAX(F193:O193),3,IF(G193=MAX(G193:O193),4,IF(H193=MAX(H193:O193),5,IF(I193=MAX(I193:O193),6,IF(J193=MAX(J193:O193),7,8)))))))

I tried to take this formula further to 12 if/thens and discovered I
can only use 7.

Any ideas? I don't have VBS knowledge if that is what it is going to
take. Thank you very much for any help.

Lou

 

[Dave R.]

Try something like this; you may have to insert some $$ if you want to copy
it.


=MATCH(MAX(D193:O193),D193:O193,0)

 

[Lou]

Dave, you are the MAN! I have labored for many hours trying to get
this work. I'm not really understanding the function, but it works!
Thank you very much!

Lou

 

[Dave R.]

Youre very welcome Lou, always nice to hear that something worked.

A quick explanation of what this does;

MAX(D193:O193) returns the maximum value in that range, say 50.

MATCH searches through the range D193:O193 for 50 [MAX(D193:O193)], and
tells you the POSITION where it finds the match. The ",0" part means it's
looking for an exact match, which is fine because the value it's looking for
(i.e. 50) was taken directly from that range with the MAX function.

if you look at MATCH under help, it's just

MATCH(Lookup value, lookup array [range], match type)

Anways, if 50, the value we look up, is in D193, the formula returns 1 since
it was in the first position of the range. If it's in the next column over,
E, then you'll get a 2 with that formula, and so on.