VLOOKUP and LARGE and IF??

[Ignition1]

Hi

I'm struggling with a formula.

Column A - BSE, EST, NWD, MNS, SNI
Column B - Project Names
Column H - Various Gross Income values

There are multiple projects for each category in Column A.

What I want to do is -

Provide the top 10 projects by gross income for BSE.
Then do the same for EST, NWD etc.

I was trying various combinations of VLOOKUP(LARGE...) or IF(LARGE...

None seem to work. Any solutions?

 

[MrKowz]

Welcome to the forums!

Try:

=LARGE(IF(A1:A100="BSE",H1:H100,""),1)
=LARGE(IF(A1:A100="BSE",H1:H100,""),2)
=LARGE(IF(A1:A100="BSE",H1:H100,""),3)
etc...

Confirm entry with CTRL+SHIFT+ENTER to store it as an array formula. You will know you enter it properly when the formula is surrounded with {brackets}.

 

[Ignition1]

Perfect! Thanks very much. But just to build on my knowledge...

I had that formula written but I was just pressing Enter (not CTRL + SHIFT + ENTER)

Why do I have enter it like this? I did a quick Google search and it's because it's an array formula...what is that?

 

[MrKowz]

Chip Pearson has a great writeup on Array formulas here: http://www.cpearson.com/excel/ArrayFormulas.aspx

 

[Ignition1]

Thanks very much.

I'm stumped on this next one though -

I need to get the Project Title and Start Date for the project in the same Top Ten table.

So essentially I need a formula which says - If the category is BSE, and the start date is in the top ten, place the start date in the appropriate row along with the project title and project reference.

For assistance - the Project titles are in column B, Category in A, Start Date in G.

 

[MrKowz]

Can you please provide some sample data, as well as your desired outcome?

 

[Ignition1]

The problem is beyond my understanding.

So I've thought of a work around -

Is there a way to display data from a data range depending on the value of a cell?

So - the data range has loads of BSE, NWD, MNS projects. But if Cell C5 on SheetData! is equal to NWD...then below it list all the NWD projects and corresponding data?