Find the corresponding cell based on a value in a range

[wayneknox01]

I have some ranked values I want to find out which 'brand' they correspond to - these are in cells D2:K2. Instead of just showing the value worked out from the formula I used (from a solution in this previous post) I need to lookup which 'brand' that value comes from is - essentially the column it is in.

You will see from the attachment below I need to find from D2:K2 which corresponds to the value in L4, I will then repeat for M4 etc.

Picture1.jpg

[Pete_UK]

Attaching a picture is not very useful as we can't work on it. You will need some kind of tie-break to distinguish between entries that are equal, as Martin was getting at in your previous post. A common way is to add or subtract a fractional value onto the value, which is dependent on the column the value is in (eg COLUMN()/10000), and then it is possible to extract the column directly.

Hope this helps.

Pete

[wayneknox01]

Sorry I guess its hard to explain and appreciate a picture doesn't help - i'm just trying to help you visualise what i'm trying to do.

The way I see it is:
I have a long list of values (hours open) across multiple columns(D3:K664) - each has a header, which are brands (D2:K2). Taking each row seperately (each row is a different location), starting with row 3, i've ranked the highest value (most hours open) and placed them in cells L3, M3, N3 etc in descening order. However in the cell I do not want to show the value of the rank (hours open), but the header it falls under, the brand (D2:K2). So ranking each brand in highest number of hours open. Then repeat this down the list for each row.

[Pete_UK]

Yes, I understood exactly what you wanted to do. The problem is that in row 4 you have two banks that are open for 7 hours and two others that are open for 5, so if you try to use HLOOKUP or INDEX/MATCH without any tie-break then you would get two entries for Barclays and two entries for HSBC, which is clearly wrong. If you posted an Excel file then we could play about with it, but you can't do much with a picture other than look at it.

Pete