reverse lookup function, index/match usage?
 

Hi, I never got a response to this question that worked, so I thought I would
ask it again hoping that this time it makes more sense:

Here is a sample of my data:
Column1 Column2 Column3 Column4
row 1 1/1/2008 2/1/2008 3/1/2008 4/1/2008
row 2 BBB AAA AAA CCC
row 3 CCC AAA BBB AAA
row 4 BBB BBB BBB CCC

I am using the codes BBB, AAA, CCC to indicate in another spreadsheet the
total hours worked on a project (project AAA, BBB, or CCC), each time a code
in entered in a cell it indicated 15 minutes of work. I am able to add up
the time per code easily enough, but what i want to do is show the first date
that the project was worked on. For example, project code AAA was first
worked on on 2/1/2008, so I want a formula that looks up "AAA" and returns
"2/1/2008". I do not want sort the spreadsheet as it is consistantly being
used to enter time worked. Any suggestions?

Thank you,
Ambie

reverse lookup function, index/match usage?
 

Try this array formula** :

F1 = AAA

=MIN(IF(A2:D4=F1,A1:D1))

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Ambie" wrote in message
...
Hi, I never got a response to this question that worked, so I thought I
would
ask it again hoping that this time it makes more sense:

Here is a sample of my data:
Column1 Column2 Column3 Column4
row 1 1/1/2008 2/1/2008 3/1/2008 4/1/2008
row 2 BBB AAA AAA CCC
row 3 CCC AAA BBB AAA
row 4 BBB BBB BBB CCC

I am using the codes BBB, AAA, CCC to indicate in another spreadsheet the
total hours worked on a project (project AAA, BBB, or CCC), each time a
code
in entered in a cell it indicated 15 minutes of work. I am able to add up
the time per code easily enough, but what i want to do is show the first
date
that the project was worked on. For example, project code AAA was first
worked on on 2/1/2008, so I want a formula that looks up "AAA" and returns
"2/1/2008". I do not want sort the spreadsheet as it is consistantly
being
used to enter time worked. Any suggestions?

Thank you,
Ambie

reverse lookup function, index/match usage?
 

YAY! Thanks, this works perfectly! Happy Holidays!

"T. Valko" wrote:

Try this array formula** :

F1 = AAA

=MIN(IF(A2:D4=F1,A1:D1))

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Ambie" wrote in message
...
Hi, I never got a response to this question that worked, so I thought I
would
ask it again hoping that this time it makes more sense:

Here is a sample of my data:
Column1 Column2 Column3 Column4
row 1 1/1/2008 2/1/2008 3/1/2008 4/1/2008
row 2 BBB AAA AAA CCC
row 3 CCC AAA BBB AAA
row 4 BBB BBB BBB CCC

I am using the codes BBB, AAA, CCC to indicate in another spreadsheet the
total hours worked on a project (project AAA, BBB, or CCC), each time a
code
in entered in a cell it indicated 15 minutes of work. I am able to add up
the time per code easily enough, but what i want to do is show the first
date
that the project was worked on. For example, project code AAA was first
worked on on 2/1/2008, so I want a formula that looks up "AAA" and returns
"2/1/2008". I do not want sort the spreadsheet as it is consistantly
being
used to enter time worked. Any suggestions?

Thank you,
Ambie

reverse lookup function, index/match usage?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ambie" wrote in message
...
YAY! Thanks, this works perfectly! Happy Holidays!

"T. Valko" wrote:

Try this array formula** :

F1 = AAA

=MIN(IF(A2:D4=F1,A1:D1))

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Ambie" wrote in message
...
Hi, I never got a response to this question that worked, so I thought I
would
ask it again hoping that this time it makes more sense:

Here is a sample of my data:
Column1 Column2 Column3 Column4
row 1 1/1/2008 2/1/2008 3/1/2008 4/1/2008
row 2 BBB AAA AAA CCC
row 3 CCC AAA BBB AAA
row 4 BBB BBB BBB CCC

I am using the codes BBB, AAA, CCC to indicate in another spreadsheet
the
total hours worked on a project (project AAA, BBB, or CCC), each time a
code
in entered in a cell it indicated 15 minutes of work. I am able to add
up
the time per code easily enough, but what i want to do is show the
first
date
that the project was worked on. For example, project code AAA was
first
worked on on 2/1/2008, so I want a formula that looks up "AAA" and
returns
"2/1/2008". I do not want sort the spreadsheet as it is consistantly
being
used to enter time worked. Any suggestions?

Thank you,
Ambie