FIND, With two coulms to compare

I

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
 
T

Trevor Shuttleworth

Something like:

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

Regards

Trevor
 
A

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.
 
A

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.

Trevor Shuttleworth said:
Something like:

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

Regards

Trevor
Click to expand...
 
I

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?
 
A

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.
 
T

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.


Aladin Akyurek said:
The ISNA bit is superfluous for C1 (which is the Max value of the B-range)
will always be available in B-range.
Click to expand...
 
I

ianripping

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

Thankyou so much!
 
Top