Matching Data in a Row and Display in Column

[grizzly1733]

Hi,

I was wondering if anyone could help me with this problem. I am a
track coach and I created a matrix to list athletes and events. I was
wondering if you could write a formula to scan a row of events and find
an event then display the athlete in a new column list by event.

Like this

Name Event 1 Event 2 Event 3 Event 4
Bob 100 200 1600 3200
Jim 400 100 1600 200
Larry 110 200 400 3200


Show a List as follows

100 200 400 1600 3200
Bob Bob Jim Bob Bob
Jim Jim Larry Jim Larry
Larry

Thank you for the help.

I have tried vlookup, hlookup, match and index with little success.

 

[Door]

As per the attached,

=IF(ISNA(MATCH(B$8,$B3:$E3,0)),"",$A3)

Hope this helps

Hi,

I was wondering if anyone could help me with this problem. I am
track coach and I created a matrix to list athletes and events. I wa
wondering if you could write a formula to scan a row of events and fin
an event then display the athlete in a new column list by event.

Like this

Name Event 1 Event 2 Event 3 Event 4
Bob 100 200 1600 3200
Jim 400 100 1600 200
Larry 110 200 400 3200


Show a List as follows

100 200 400 1600 3200
Bob Bob Jim Bob Bob
Jim Jim Larry Jim Larry
Larry

Thank you for the help.

I have tried vlookup, hlookup, match and index with little success

+-------------------------------------------------------------------
|Filename: Contestant.zip
|Download: http://www.excelforum.com/attachment.php?postid=4562
+-------------------------------------------------------------------

 

[grizzly1733]

Thanks Door,

It works

you're awesome

matt

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

 

[Door]

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 eac
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 t
E)

,"", is the 'true' for ISNA - put blank where the event is not foun
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 the
downwards for the required number of rows, the correct cell reference
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 the
remember to copy the formula, currently B12:H12 down more rows (Selec
B12:H12, and drag the small + in the bottom right corner of th
highlight to repeat the formulas on more rows)
You need at least as many rows as you have contestants, more rows wil
simply reflect blanks (ie, it is better to have more rows in the secon
block than less rows)

Hope this helps.