Semi complex formula (I'm guessing)

[Michael]

Hi all,
Here's the deal:
I have a row of data (3) and in that row I want to search for a value
(S). When that value is found, I want to return the value of the first
cell in the column in which the value (S) was found.

So, the formula in cell c2 is to search in range c3:ch3, for "s". When
"s" is found, say in column h, returns a the value in H1.

I'm thinking that I need to use Lookup and index, but am not sure how
to get the information I want.

Any help is appreciated.

Thanks,
Mike

 

[Frank Kabel]

Hi
use
=INDEX(C1:CH1,MATCH("s",C3:CH1,0))

 

[Michael]

Thanks, Frank, but that wasn't quite what I was getting at. Here's a
picture:
A B C D E F G H I
1 Start Due 1/1/04 1/2/04 1/3/04 1/4/04 1/5/04 1/6/04 1/7/04
2 1/1/04 1/3/04 S D
3 S D
4 S
5
6 S
I'm looking for a formula for A2 and B2 that will search row 2 for "s"
(or "d")and return the value for the column in which the "S" or "d" is
found from row 1, in this case, a date, or as in the example, 1/1/04
and 1/3/04.

Thanks,
Mike

 

[David Benson]

Mike,

Frank was close -- the error was probably just a typo.

The correct formula is:

A2: =INDEX(C$1:CH$1,1,MATCH("S",C2:CH2,0))
B2: =INDEX(C$1:CH$1,1,MATCH("D",C2:CH2,0))

I tried this out -- assuming that in Row 2, the "S" is in Column C and the
"D" is in Column E, it works.

-- David

 

[Michael]

David and Frank--
You guys are the bomb! This worked perfectly and so I was wrong, it
was a simple formula--I was just thinking about it as complex--THANK
YOU THANK YOU THANK YOU!

Mike

 

[Michael]

Now that this works (thanks, again, guys), I'd like to use the
results of the formulas in A2 and B2 in another cell to calculate the
number of work days in betwixt the two dates (I've added contents to
the weekend cells and any holidays so that I could use a countblank)
but I'm not sure how to use the results of A2 and B2 as a range. Is it
possible?

Cheers,
Mike