Return cell address of value in array

Dave Reed

New Member
Aug 9, 2010
Excel 97

A 40 row two column list of items made up of (col a) a text label (say apples & (col b) a count of the occurances of the text label in an external table. - all ok upto this point

Need to identify the top 10 text counts, so say the most common item occurs 15 times, I can identify this using the large function, what I cannot do is tell what the number refers to, so if I was able to return the cell address the large function got the number from I could work it out.

This also need to be able to handle duplicates (ie a count of 2 in 5th & 6th position).

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)


Feb 18, 2002
Hi and welcome to the Board!!!
Have you considered a Pivot Table??


Dave Reed

New Member
Aug 9, 2010
I was hoping to be able to do it with a function, thinking along the lines of excel must know the cell address it took the value from, just want it to return that cell address.

had not thought of a pivot table - will check out.

Thanks for the fast reply from the other side of the water
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...