Greetings.



I have an excel worksheet where details of matches are stored. Each record has 16 columns that are reserved for player names. I need a procedure or a function that will search/find the first occurrence of a given variable (player name) , then the cell pointer ( or ActiveCell) always moves to column B of the row found with the player name to store it in another worksheet.

I used the offset method to go back to column 2 , but the offset could be different in each case, and this is the part that I failed to code.



Here is a simple presentation of the worksheet:



A B C D E F G H

1 SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4

2 1 02/11/04 WCQ USA Jones Hall Victor Smith

3 2 04/11/04 WCQ Chile Jones Hall Brown Cobby

4 3 06/11/04 WCQ Mexico Shaw Brown Green Victor

5



Example: Lookup Cobby (in this case at range H3) and the stored variable should be range B3 ) date 04/11/04) which is CellOffset - 6 , but If I lookup "Green" , the CellOffset would be - 5



I hope the problem is clear . Appreciate any help or hint.



Thanks.

Majeed,
I didn't find a simple solution, but here's a two-step process that may be off use. First, find the first occurrence of, say, Brown, in each row, then find which column contains the first occurence of Brown.

At the end of your player data, in cells under "WORK", I added the formulas
=HLOOKUP($A$8,E3:H3,1,FALSE)
=HLOOKUP($A$8,E4:H4,1,FALSE)
=HLOOKUP($A$8,E5:H5,1,FALSE)
Cell A8 contains the name of the player to look for.
Cells E3:H5 contain the names of all players.

Cell A9, "1st Date", is, I believe, what you are after. The formula is
=INDEX(B3:B5,MATCH(A8,I3:I5,0))
That's a combination of two formulas:
=MATCH(A8,I3:I5,0)
finds the first occurrence of the desired player in the WORK cells, and returns the relative row number.
The index function returns the date in that row.

Hope that helps.
Chris King

A B C D E F G H
SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4 WORK
1 2/11/2004 WCQ USA Jones Hall Victor Smith #N/A
2 4/11/2004 WCQ Chile Jones Hall Brown Cobby Brown
3 6/11/2004 WCQ Mexico Shaw Brown Green Victor Brown

Player 1st Date
Brown 4/11/04


"Majeed" wrote in message ...
Greetings.



I have an excel worksheet where details of matches are stored. Each record has 16 columns that are reserved for player names. I need a procedure or a function that will search/find the first occurrence of a given variable (player name) , then the cell pointer ( or ActiveCell) always moves to column B of the row found with the player name to store it in another worksheet.

I used the offset method to go back to column 2 , but the offset could be different in each case, and this is the part that I failed to code.



Here is a simple presentation of the worksheet:



A B C D E F G H

1 SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4

2 1 02/11/04 WCQ USA Jones Hall Victor Smith

3 2 04/11/04 WCQ Chile Jones Hall Brown Cobby

4 3 06/11/04 WCQ Mexico Shaw Brown Green Victor

5



Example: Lookup Cobby (in this case at range H3) and the stored variable should be range B3 ) date 04/11/04) which is CellOffset - 6 , but If I lookup "Green" , the CellOffset would be - 5



I hope the problem is clear . Appreciate any help or hint.



Thanks.