• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using LARGE and returning corresponding cell info

K

Kellin

New Member
Hi - looking for some help please!

I have used LARGE to tell me what the top 5 numbers are within a column, but I want to report on the corresponding invoice number in column A. I have tried to use MATCH but it matches the top number only, I cannot get it to look at the top 5 in column M then look at Column A and tell me the invoice for each number.

I hope that is clear enough!

Thanks in advance.
 
Kellin
Without Your Excel sample file -- -- -- could this be clear enough? ... no!
... and please, You should give as well as Your expected results too.
 
Hi @vletm

Thanks for that - Sorry, I didn't specify this requirement in my sample spreadsheet as I hadn't even thought about using AutoFilter. I actually want to pull the top five out and report them on a separate worksheet, so I would need a formula for that unless AutoFilter can report the data other than in the list?
 
Kellin
... after AutoFilter
... copy and paste ...
or
... report as it is
... where do You need two worksheets?
... ... one sheet which could use many ways?

Aren't there Your needed top 5 and so?

What else You didn't write?
What was Your sample file?
Was that Your sample clear?
 
I have raw data on one page and the front page is the summary data. So I have lots of formulae on the raw data page, then I pull that data onto my summary page. All I wanted to know was whether I could use LARGE and reference the cell across from it - is that not possible?
 
Although I use Excel it is 365 and so may be a completely different program from the one you use.
Code: 
= LET(
  top_5, RANK(Elapsed,Elapsed)<=5,
  record, IF({1,0},Company, Elapsed),
  SORT(FILTER(record, top_5),2,-1))
73819
 
1] Although B4 and B5 look equal to 8, in fact these cells formatted to "Number" and in "0 decimal place".

B4 and B5 are in lay difference as in 7.89798611111473 and 7.89712962962949.

2] Index and Match function is enough for this layout.

So,

3] In A2, formula copied down :

=INDEX(Sheet1!B:B,MATCH(B2,Sheet1!E:E,0))

Regards
 
Last edited:
Kellin
... or this way with Your sample file
Select Sheet1 > select Sheet2 to get results.
 

Attachments

  • Sample.xlsb
    24.8 KB · Views: 2
bosco_yip said:
1] Although B4 and B5 look equal to 8, in fact these cells formatted to "Number" and in "0 decimal place".

B4 and B5 are in lay difference as in 7.89798611111473 and 7.89712962962949.

2] Index and Match function is enough for this layout.

So,

3] In A2, formula copied down :

=INDEX(Sheet1!B:B,MATCH(B2,Sheet1!E:E,0))

Regards
Click to expand...
Ah @bosco_yip, that's it! Absolutely brilliant, exactly what I was after - thank you very much indeed!
 
You must log in or register to reply here.
Back
Top