Lookups and Sorting

[ChuckUK]

I have a document with two worksheets, named alpha and bravo.

On sheet alpha I have a simple three column table;

A B C
1 Peter 5 6
2 David 12 9
3 Mike 7 2
4 Fred 2 7
5 Peter 9 1

On sheet bravo, i want to enter a formula that looks at sheet alpha
then looks for Mike and then tells me the number next to that person.
have found a way to do this using the lookup function like this;

=LOOKUP("Mike",alpha!a1:a5,alpha!b1:b5)
=LOOKUP("Mike",alpha!a1:a5,alpha!c1:c5)

Where I am having problems is if the person is named twice, I need th
formula to display the highest number of the two people from the colum
I choose, let's say column B.

So for Peter I would want the number 9 on column b and next to it th
corresponding number from column c, 1.

I don't want to sort any of the data on sheet alpha, so all the wor
needs to be done on sheet bravo

 

[Max]

One way ..

Assuming the sample table in sheet: alpha
is in A1:C5, and there''ll be no ties for the numbers
in col B for any particular name in col A

In Sheet: bravo
----------------------
With the target name (i.e. Peter, Mike, ..)
entered in cell A1
(perhaps better than "hardcoded" in the formula)

Put in B1: =MAX(IF(TRIM(alpha!$A$1:$A$5)=A1,alpha!$B$1:$B$5))
Array-enter the formula (Press CTRL+SHIFT+ENTER)

Put in C1:
=INDEX(alpha!$C$1:$C$5,MATCH(1,(TRIM(alpha!$A$1:$A$5)=A1)*(alpha!$B$1:$B$5=B
1),0))
Array-enter the formula (Press CTRL+SHIFT+ENTER)

For the sample data in sheet: alpha,
if in A1: Peter
B1 will return: 9
C1 will return: 1

 

[Domenic]

Also...

bravo!B1:

=INDEX(alpha!C1:C5,MATCH(MAX(IF(alpha!A1:A5=bravo!A1,alpha!B1:B5)),alpha!B1:B5,0))

...entered using CTRL+SHIFT+ENTER, where bravo!A1 contains the name o
interest.

Hope this helps

 

[ChuckUK]

Thanks guys B1 works fine.

But the code for C1 gives me a error.

Also, what do you mean by ties?
The data in column A1 to A5 has to allways match the corresponding dat
in B1 to 5 and C1 to 5

 

[Max]

But the code for C1 gives me a error.

Whose formula did you try in C1?

Also, what do you mean by ties?

If the data in sheet: alpha is instead ..

A B C
1 Peter 9 6
2 David 12 9
3 Mike 7 2
4 Fred 2 7
5 Peter 9 1

then col B's# for Peter would have a tie

In the case above,
in sheet: bravo
with "Peter" in A1
the formula in B1 would return the 1st "9"
and the formula in C1 would return "6"

The caveat was pre-emptive,
just in case you might this situation arising

 

[Max]

Correction ..:

just in case you might this situation arising

should read:

 

[Max]

think if the OP's data in alpha had been:

A B C
1 Peter 5 6
2 David 12 9
3 Mike 9 2
4 Fred 2 7
5 Peter 9 1

where Mike also had a "9" in col B (instead of the orig "7")

the formula suggested below
(meant for C1 in sheet: bravo instead of B1, I believe)
would have returned Mike's col C: "2" instead of Peter's "1"

 

[Domenic]

Good one Max! Thanks for catching my mistake!

Cheers!

 

[Max]

You're welcome, <g> !