Excel - How to move text with calculated field

[blondie41]

On one worksheet I have a list of names in column A and various values in column b.

On my other worksheet I have a list of the "top 5" (numbers from column b)by using the LARGE function. This works very well, but I would like to bring the "Names" that correspond with the values as well.

I have tried the MATCH function, but it does not seem to work (I think because of the LARGE function). Does anyone have any ideas?

Thanks-

 

[pbrodsky]

Match actually will work (I tried it). You need something like
=OFFSET(<cell just before values to retrieve>,MATCH(<value from top 5>,<range of top 5 values>,0),0))

 

[DaleWatson123321]

pbrodsky,

Thanks for your contribution, but is it possible that you can provide more specifics. I too have experienced difficulty in achieving correct results with your proposed solution.

Although it might not be "likely", one should always "allow for the unexpected" - in this case the possibility of there being numbers that produce "ties" - for 1st place, 2nd place, or 3rd place, etc.

Here's an example of a WHAT-IF (made simple)...

Highest #s: 1,000, 800, 800, 700, 700, 700, 700, 600.

In this example, there are 7 numbers that qualify as "the top 5". All but #600 qualify - because all the 700's tie for 4th and 5th place.

If anyone can offer a solution using the MATCH and OFFSET function (or another method), please "show us the way"

If required or if anyone is interested, I can provide a solution involving Excel's Advanced Filter function that will extract "the top 5" (including any ties, and both Numbers and matching Names) to a separate sheet (or to wherever required).

Regards, Dale Watson

 

[byundt]

Let's suppose the top 5 scores are listed in cells D15, with the source data in A1:B20. You can retrieve the names corresponding to the top scores with the array formula:
=INDEX(A$1:A$20,SMALL(IF(B$1:B$20=D1,ROW(B$1:B$20),""),COUNTIF(D$11,D1)))
Array formula, so remember to Control + Shift + Enter