A3:E5 is your data in the form Person, Event, Event, Event etc

B10:H12 is a row for each contestant (from A3:A5) and a column for each
event (the purple cells, you need to type in each event name)

=IF(ISNA(MATCH(B$8,$B3:$E3,0)),"",$A3) to
=IF(ISNA(MATCH(H$8,$B5:$E5,0)),"",$A5)

If(ISNA is to ignore any #N/A items

Match says find B$8 (the event) in $B$3:$E$3 (along the row 3 from B to
E)

,"", is the 'true' for ISNA - put blank where the event is not found
on the row

,$A3 is pick up the Name from column A or the row in question

the $ is so that when the formula is dragged along the row, and then
downwards for the required number of rows, the correct cell references
will remain.

If a contestant enters more events try
=IF(ISNA(MATCH(B$8,$B3:$Z3,0)),"",$A3)
where $Z is the last column to search.
(put that in B10 and formula-drag it over all required cells/rows)

You can insert more contestant lines (at line 6, Insert) but then
remember to copy the formula, currently B12:H12 down more rows (Select
B12:H12, and drag the small + in the bottom right corner of the
highlight to repeat the formulas on more rows)
You need at least as many rows as you have contestants, more rows will
simply reflect blanks (ie, it is better to have more rows in the second
block than less rows)

Hope this helps.



grizzly1733 Wrote:
Thanks Door,

It works

P.S. Any chance you could explain how it works?


--
Door
------------------------------------------------------------------------
Door's Profile: http://www.excelforum.com/member.php...o&userid=33068
View this thread: http://www.excelforum.com/showthread...hreadid=528822