# Lookups and Sorting

C

#### 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

M

#### 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

D

#### 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

C

#### 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

M

#### 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

M

#### Max

Correction ..:
just in case you might this situation arising

M

#### 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"

D

#### Domenic

Good one Max! Thanks for catching my mistake!

Cheers!