LOOKUP (or IF?)

[Stiggs]

Hello all,

Using Excel for the first time in about 2 years and i'm alarmed at how much i have forgotten! I'd be really grateful if anybody could assist me.

I'm using a tabbed worksheet (see attached). On the left hand side of tab 1 i have listed various criteria. In column B each of the criteria will be judged either 'vital', 'important' or 'nice'.

The tricky bit! If a criteria is judged 'vital', i need it to be fed through to sheet 2, in both cells B4 and C3. The next criteria that is judged 'vital' would need to go into cells B5 and D3 and so on. If a criteria is judged 'important', i need it to be fed through to sheet 3, in both cells B4 and C3. The next criteria that is 'important' would need to go to sheet 3 cells B5 and D3. Etcetera etcetera!

Is this possible with a formula, or will i have to do it manually?

Many thanks in advance for your help guys, i've been tearing my hair out playing with LOOKUPS and IF for ages.

[NBVC]

See attached...

First formula added in C4 of Sheet1:

=COUNTIF($B$4:B4,"Vital")

copied down to count the number of Vital entries.

Then in sheet 2, A4 added formula to give max number of Vital in sheet1...

=MAX(Sheet1!C:C)

Then in B4:

=IF(ROWS($B$4:$B4)>$A$4,"",INDEX(Sheet1!$A:$A,MATCH(ROWS($B$4:$B4),Sheet1!$C:$C,0)))

copied down..

Not sure if you wanted the same exact thing to show up in C3.. a little confusing... but if you do, then in C3:

=IF(COLUMNS($B$4:B$4)>$A$4,"",INDEX(Sheet1!$A:$A,MATCH(COLUMNS($B$4:B$4),Sheet1!$C:$C,0)))

copied across.

[Stiggs]

Thanks so, so much friend. That's exactly what i was after. Excellent!