Solved: Populate Column based on contents of range

[DekHog]

Hi all...

Can anyone help with this? I tried posting it in an Excel Forum (thought it would be a formula rather than VBA) but got no replies despite many views...

The title of the post basically explains the problem, and trying to explain it here would be nigh on impossible, so I've attached the xls with the problem in a text box in it.

I've also populated two of the cells in the 'Level' column to assist.

Thanks to all...

[rory]

It's not pretty but you can put this in F2 and copy down:

=IF(COUNTIF($L$2:$O$10,E2)=0,"",INDEX($K$2:$K$10,SUMPRODUCT((($L$2:$L$10=E2 )+($M$2:$M$10=E2)+($N$2:$N$10=E2)+($O$2:$O$10=E2))*(ROW($L$2:$L$10)-1))))

HTH
Rory

[rory]

PS That assumes there will only ever be one match in the table in L2:O10 for any entry in column E.

[DekHog]

Thanks, it works a treat... but I wish I knew how you actually start writing a formula like that... I wouldn't even attempt to edit it, never mind write it!!