Help!

[Ignition1]

Hi

Trying to list some data corresponding with descending data in a different column.

So basically -

Column G is sorted descending, I want to list the values in Column B corresponding to the top 10 values in G.

I've managed to get top 10 list of values which changes depending on what is selected in a drop down box - but that was easy because they are numbers. I need to list the corresponding project names for those numbers (and also make sure the project name changes to the correct ones when the values change).

So basically it looks like it needs to list the Project Name depending on the row.

Help!

 

[rs2k]

Have you tried VLOOKUP?

 

[Ignition1]

How would I set that up?

On Sheet 1 I've managed to make a list of values sorted using a =LARGE formula - that list is taken from Sheet 2.

The VLOOKUP will need to look at what row the value on Sheet 1 is from in Sheet 2, then grab whatever is corresponding to that.

I just tried a VLOOKUP and it returned the wrong Project from Column B (completely wrong!).

Edit - Looking through the Error value the VLOOKUP returns, it appears the issue is with the value on Sheet 1 (the LARGE formula).

 

[rs2k]

Can you provide a sample of how your data is laid out?

 

[Ignition1]

Sheet 1

Selection box at the top - changes between various UK Regions (South-East, Midlands etc). When you change this box it changes another cell on Sheet 3 to match. (I.E. Select South-East in drop down, changes Cell C1 in Sheet 3 to South-East).

Below that I've managed to list out revenues for projects corresponding to that region, in descending order.
This is the formula -
=LARGE(IF('Sheet 2'!$B$2:$B$1238=Sheet3!C1,'Sheet2'!H2:H1194),1)

Column B in Sheet 2 is like this -
South East
North West
Midlands
Midlands
South East
North West
Midlands

Column H in Sheet 2 lists project revenues.
This formula will list the project revenues by size (ONLY ones which match whatever is in Sheet 3, cell C1).

What I'm trying to do is get the Project Name (Column C on Sheet 2) which corresponds to the revenue figure.

Only problem is - there are several identical revenue figures.

If there is a formula which looks at the ROW that the revenue figure is on in Sheet 2, and then grabs the matching value (text) from Column C, Row (X). I'm assuming this would continue to work when I also change the Region in the drop down box?

My limited Excel knowledge would hazard a guess at some mixture of INDEX and MATCH?

 

[rs2k]

I'm still not sure how this is all laid out. I have sent you a PM with my email address if you want to send me a copy of your workbook - please remove all sensitive information.