# Find highest value in unsorted column and return data of other cell

1. ## Find highest value in unsorted column and return data of other cell

Hi All,

Here is the example

COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
10 56 60 12
20 86 12 8
30 16 80 60
40 5 15 4

Its a 4 column table and i need to look up the highest value in column 4 and return the data of column 3 in a cell. I tried with VLOOKUP but it does not work since the column is not sorted. Is there another way.

Brgds,

2. ## Re: Find highest value in unsorted column and return data of other cell

=INDEX(\$C\$1:\$C\$10,MATCH(MAX(\$D\$1:\$D\$10),\$D\$1:\$D\$10,0)) should do it

3. ## Re: Find highest value in unsorted column and return data of other cell

Perhaps
``Please Login or Register  to view this content.``
?

4. ## Re: Find highest value in unsorted column and return data of other cell

Thanks guys works like a charm!

5. ## Re: Find highest value in unsorted column and return data of other cell

...another thing....how can i change this formula so it will lookup the second highest value and give the data also of the column 3

6. ## Re: Find highest value in unsorted column and return data of other cell

Hi vergrootglas

Use the LARGE function

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Find highest value in unsorted column and return data of other cell

Thanks to all of you....works perfect now, just what i needed!

8. ## Re: Find highest value in unsorted column and return data of other cell

try

=INDEX(\$C\$1:\$C\$10,MATCH(LARGE(\$D\$1:\$D\$10,2),\$D\$1:\$D\$10,0))

Change the highlighted portion according as per largest(1), 2nd largest(2), 3rd largest(3) and so on...

9. ## Re: Find highest value in unsorted column and return data of other cell

Your second largest is column 4 is zero and you have got 6 and 8 coresponding.
Are you happy with the first return as proposed?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1