FIND, With two coulms to compare

[ianripping]

I think for this I need to use FIND function. Ill leave it up to you.

Coulmn A

Ian
Micheal
Bon

Column B

77
80
9

Column C

Highest Value = 80

I need Cell D1 to display Micheal, because this person has the highes
amount in the adjacent column.

Any ideas

 

[Trevor Shuttleworth]

Something like:

=IF(ISNA(MATCH(C1,B1:B3)),"Not found",INDEX(A1:A3,MATCH(C1,B1:B3)))

Regards

Trevor

 

[Aladin Akyurek]

Let A1:B4 house the sample you provided with A1:B1 housing appropriate
labels like Name and Score. The *regular* answer/solution is invariably...

=INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0))

However, lets consider the following sample

{"Name","Score";"Ian",77;"Micheal",80;"Bon",9;"Mike",80;"Jane",80;"Bill",77;
"Eugene",37;"Marian",32}

in A1:B9. It's now not that obvious that the outcome is just Michael. Try
the following instead:

In C1 enter: Rank [ just a label ]

In C2 enter & copy down:

=RANK(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1

C-range will look now like this:

{"Rank";4;1;8;2;3;5;6;7}

In E1 enter:

=MAX(B2:B9)

In E2 enter: 1 [ N highest, where N is set to 1]

In E3 enter:

=MAX(IF(INDEX(B2:B9,MATCH(E2,C2:C9,0))=B2:B9,C2:C9))-E2

which must be confirmed with control+shift+enter instead of just with enter.

In E4 enter: H-List [ just a label ]

In E5 enter & copy down:

=IF(ROW()-ROW($E$5)+1<=$E$2+$E$3,INDEX($A$2:$A$9,MATCH(ROW()-ROW($E$5)+1,$C$
2:$C$9,0)),"")

E-range would now look like this:

{80;1;2;"H-List";"Micheal";"Mike";"Jane";"";"";"";"";"";""}

As it's clear from above, the answer set consists of Michael, Mike, and
Jane.

 

[Aladin Akyurek]

The ISNA bit is superfluous for C1 (which is the Max value of the B-range)
will always be available in B-range.

Something like:

=IF(ISNA(MATCH(C1,B1:B3)),"Not found",INDEX(A1:A3,MATCH(C1,B1:B3)))

Regards

Trevor

 

[ianripping]

I've tried this but in my C1 Cell I have the formula Max(B1:B3). This
comes out with the hightest value in column B. And so the thing works.

But when I change the numbers in B, it doest match everything
correctly. It behaves very wierd. Any idea?

 

[Guest]

Thanx, I eperienced the same problem . Thanx
(e-mail address removed)

 

[Aladin Akyurek]

It should be:

=INDEX(A2:A4,MATCH(C1,B2:B4,0))

or

=INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0))

However, see my first post regarding multiple instances of the Max value,
which is more often than not overlooked.

 

[Trevor Shuttleworth]

The OP didn't actually say how the cell C1 is populated. I assumed that it
might be any value and catered for that condition. Probably better safe
than sorry and, for the one formula, it isn't going to slow the calculation
down tremendously. But, given that you're assumption is correct, you are
right that it is superfluous.

The ISNA bit is superfluous for C1 (which is the Max value of the B-range)
will always be available in B-range.

 

[Trevor Shuttleworth]

In what way weird ?

 

[ianripping]

=INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0))
This works great as it is.

Thankyou so much!