software

i have a sheet containing daes in column a, a name in column b and
another name in column cno column b name will be reeated for each date.

i want to get the name in column c by matching the name in column b and
date in column aa b c
1/2/6 bob fizz
1/2/6 pete fizz
2/2/6 bob pepsi
2/2/6 steff orange
2/2/6 pete pepsi
3/2/6 bob lemonfrom the example i want to use the name bob and the date 2/2/6 to get
orange.--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=534329=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

ceemo wrote:
gt;
gt; i have a sheet containing daes in column a, a name in column b and
gt; another name in column c
gt;
gt; no column b name will be reeated for each date.
gt;
gt; i want to get the name in column c by matching the name in column b and
gt; date in column a
gt;
gt; a b c
gt; 1/2/6 bob fizz
gt; 1/2/6 pete fizz
gt; 2/2/6 bob pepsi
gt; 2/2/6 steff orange
gt; 2/2/6 pete pepsi
gt; 3/2/6 bob lemon
gt;
gt; from the example i want to use the name bob and the date 2/2/6 to get
gt; orange.
gt;
gt; --
gt; ceemo
gt; ------------------------------------------------------------------------
gt; ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
gt; View this thread: www.excelforum.com/showthread...hreadid=534329

--

Dave Peterson


thank you for your help--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=534329
i couldnt get this to work=index(othersheet!$c$1:$c$100,match(1,(a2=othershe et!$a$1:$a$100)*(b2=othersheet
!$b$1:$b$100),0))

would it not be something similar to this

=index(othersheet!$c$1:$c$100,match(1,(othersheet! $a$1:$a$100=quot;2/2/6quot;)*(othersheet
!$b$1:$b$100=quot;bobquot;),0))

?--
ceemo
------------------------------------------------------------------------
ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
View this thread: www.excelforum.com/showthread...hreadid=534329Is that 2/2/6 a date?

if yes and it means February 2, 2006, then maybe:

=index(othersheet!$c$1:$c$100,
match(1,(othersheet!$a$1:$a$100=date(2006,2,2))
*(othersheet!$b$1:$b$100=quot;bobquot;),0))
ceemo wrote:
gt;
gt; i couldnt get this to work
gt;
gt; =index(othersheet!$c$1:$c$100,match(1,(a2=othershe et!$a$1:$a$100)*(b2=othersheet
gt; !$b$1:$b$100),0))
gt;
gt; would it not be something similar to this
gt;
gt; =index(othersheet!$c$1:$c$100,match(1,(othersheet! $a$1:$a$100=quot;2/2/6quot;)*(othersheet
gt; !$b$1:$b$100=quot;bobquot;),0))
gt;
gt; ?
gt;
gt; --
gt; ceemo
gt; ------------------------------------------------------------------------
gt; ceemo's Profile: www.excelforum.com/member.php...oamp;userid=10650
gt; View this thread: www.excelforum.com/showthread...hreadid=534329

--

Dave Peterson