I am trying to retreive a date from one worksheet and place it on another.
Most often, I can match on company name, but there are times when the company
name is not unique and I have to use first/last name. The following is an
example of data on the worksheet I am trying to retreive from:


Acutech 12/01/05 N Ms. Linda Dendy
Adams Simpson LLP 12/01/05 N Mr. Jesse Evans
Adams Simpson LLP 01/01/06 N Ms. Deborah Hembree

I am using the following: =INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).
This will work and find the match, pick the date (from the second column),
but in the case where the company is Adams Simpson LLP, it will always choose
the 12/01/05 date. I need to find both. So I would need to match on the
first and last name if there is more than one entry of the company.

Can this be done (simply)? Thank you.

--
hgopp99
--
hgopp99

You can use this kind of syntax:

=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.

hgopp99 wrote:

I am trying to retreive a date from one worksheet and place it on another.
Most often, I can match on company name, but there are times when the company
name is not unique and I have to use first/last name. The following is an
example of data on the worksheet I am trying to retreive from:


Acutech 12/01/05 N Ms. Linda Dendy
Adams Simpson LLP 12/01/05 N Mr. Jesse Evans
Adams Simpson LLP 01/01/06 N Ms. Deborah Hembree

I am using the following: =INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).
This will work and find the match, pick the date (from the second column),
but in the case where the company is Adams Simpson LLP, it will always choose
the 12/01/05 date. I need to find both. So I would need to match on the
first and last name if there is more than one entry of the company.

Can this be done (simply)? Thank you.

--
hgopp99
--
hgopp99

--

Dave Peterson

Can you explain, in more detail, what the $A, $B, and $C represent? Also
what did (one cell) mean? I did not understand your reply.

I am trying to get the date, which is in the second column of one sheet to
the first column of another. The company, which is one field they have in
common is in the third column of the first sheet and the first column of the
sheet that I am trying to retreive my data. That is why I was originally
using:
=INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).

--
hgopp99


"Dave Peterson" wrote:

You can use this kind of syntax:

=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.

hgopp99 wrote:

I am trying to retreive a date from one worksheet and place it on another.
Most often, I can match on company name, but there are times when the company
name is not unique and I have to use first/last name. The following is an
example of data on the worksheet I am trying to retreive from:


Acutech 12/01/05 N Ms. Linda Dendy
Adams Simpson LLP 12/01/05 N Mr. Jesse Evans
Adams Simpson LLP 01/01/06 N Ms. Deborah Hembree

I am using the following: =INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).
This will work and find the match, pick the date (from the second column),
but in the case where the company is Adams Simpson LLP, it will always choose
the 12/01/05 date. I need to find both. So I would need to match on the
first and last name if there is more than one entry of the company.

Can this be done (simply)? Thank you.

--
hgopp99
--
hgopp99

--

Dave Peterson

This formula would wrap in the newsgroup post. So I split it into two lines:

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

But when you use it, it would go into one cell--not two separate cells. (That
was the easy part <bg.)

Say I want to do essentially a =vlookup() based on two different cells. My
table has the key values in column A and column B (and it's on a worksheet named
OtherSheet.

And I want to bring back the value from column C of othersheet.

So I put the value to match othersheet column A in A2 of my current worksheet.

I put the value to match column B in B2 of my current worksheet.

Then that formula will look through all of othersheet and find the first row
where both column A and column B match my input. Then it'll bring back the
stuff in column C.

I used $a$1:$a$100, $b1:$b$100, and $c$1:$c$100 to hold that table (on
OtherSheet).

I'm gonna stick with a 100 rows, but you can make it as large as you want--but
not the whole column:

In the table:
Company name is in column A.
First name is in column E.
Last name is in column F.
And the data you want to bring back is in column B(??)
(difficult for me to see, but you can change it to what you need)

On your input sheet:
company name is in column C.
first name is in column X (you didn't say, or I missed it)
lastname is in column Y (You didn't say, or did I miss it again)

So that formula (all one cell again becomes something like:

=index(othersheet!$b$1:$b$100,
match(1,(c2=othersheet!$a$1:$a$100)
*(x2=othersheet!$e$1:$e$100)
*(y2=othersheet!$F$1:$f$100),0))


Remember...

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.



hgopp99 wrote:

Can you explain, in more detail, what the $A, $B, and $C represent? Also
what did (one cell) mean? I did not understand your reply.

I am trying to get the date, which is in the second column of one sheet to
the first column of another. The company, which is one field they have in
common is in the third column of the first sheet and the first column of the
sheet that I am trying to retreive my data. That is why I was originally
using:
=INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).

--
hgopp99

"Dave Peterson" wrote:

You can use this kind of syntax:

=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.

hgopp99 wrote:

I am trying to retreive a date from one worksheet and place it on another.
Most often, I can match on company name, but there are times when the company
name is not unique and I have to use first/last name. The following is an
example of data on the worksheet I am trying to retreive from:


Acutech 12/01/05 N Ms. Linda Dendy
Adams Simpson LLP 12/01/05 N Mr. Jesse Evans
Adams Simpson LLP 01/01/06 N Ms. Deborah Hembree

I am using the following: =INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).
This will work and find the match, pick the date (from the second column),
but in the case where the company is Adams Simpson LLP, it will always choose
the 12/01/05 date. I need to find both. So I would need to match on the
first and last name if there is more than one entry of the company.

Can this be done (simply)? Thank you.

--
hgopp99
--
hgopp99

--

Dave Peterson


--

Dave Peterson

Thank you for the breakdown. I haven't tried it yet, but will before the
evening is over.
--
hgopp99


"Dave Peterson" wrote:

This formula would wrap in the newsgroup post. So I split it into two lines:

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

But when you use it, it would go into one cell--not two separate cells. (That
was the easy part <bg.)

Say I want to do essentially a =vlookup() based on two different cells. My
table has the key values in column A and column B (and it's on a worksheet named
OtherSheet.

And I want to bring back the value from column C of othersheet.

So I put the value to match othersheet column A in A2 of my current worksheet.

I put the value to match column B in B2 of my current worksheet.

Then that formula will look through all of othersheet and find the first row
where both column A and column B match my input. Then it'll bring back the
stuff in column C.

I used $a$1:$a$100, $b1:$b$100, and $c$1:$c$100 to hold that table (on
OtherSheet).

I'm gonna stick with a 100 rows, but you can make it as large as you want--but
not the whole column:

In the table:
Company name is in column A.
First name is in column E.
Last name is in column F.
And the data you want to bring back is in column B(??)
(difficult for me to see, but you can change it to what you need)

On your input sheet:
company name is in column C.
first name is in column X (you didn't say, or I missed it)
lastname is in column Y (You didn't say, or did I miss it again)

So that formula (all one cell again becomes something like:

=index(othersheet!$b$1:$b$100,
match(1,(c2=othersheet!$a$1:$a$100)
*(x2=othersheet!$e$1:$e$100)
*(y2=othersheet!$F$1:$f$100),0))


Remember...

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.



hgopp99 wrote:

Can you explain, in more detail, what the $A, $B, and $C represent? Also
what did (one cell) mean? I did not understand your reply.

I am trying to get the date, which is in the second column of one sheet to
the first column of another. The company, which is one field they have in
common is in the third column of the first sheet and the first column of the
sheet that I am trying to retreive my data. That is why I was originally
using:
=INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).

--
hgopp99

"Dave Peterson" wrote:

You can use this kind of syntax:

=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.

hgopp99 wrote:

I am trying to retreive a date from one worksheet and place it on another.
Most often, I can match on company name, but there are times when the company
name is not unique and I have to use first/last name. The following is an
example of data on the worksheet I am trying to retreive from:


Acutech 12/01/05 N Ms. Linda Dendy
Adams Simpson LLP 12/01/05 N Mr. Jesse Evans
Adams Simpson LLP 01/01/06 N Ms. Deborah Hembree

I am using the following: =INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).
This will work and find the match, pick the date (from the second column),
but in the case where the company is Adams Simpson LLP, it will always choose
the 12/01/05 date. I need to find both. So I would need to match on the
first and last name if there is more than one entry of the company.

Can this be done (simply)? Thank you.

--
hgopp99
--
hgopp99

--

Dave Peterson


--

Dave Peterson

Thank you for your patience. I now better understand the INDEX/MATCH array.
--
hgopp99


"hgopp99" wrote:

Thank you for the breakdown. I haven't tried it yet, but will before the
evening is over.
--
hgopp99


"Dave Peterson" wrote:

This formula would wrap in the newsgroup post. So I split it into two lines:

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

But when you use it, it would go into one cell--not two separate cells. (That
was the easy part <bg.)

Say I want to do essentially a =vlookup() based on two different cells. My
table has the key values in column A and column B (and it's on a worksheet named
OtherSheet.

And I want to bring back the value from column C of othersheet.

So I put the value to match othersheet column A in A2 of my current worksheet.

I put the value to match column B in B2 of my current worksheet.

Then that formula will look through all of othersheet and find the first row
where both column A and column B match my input. Then it'll bring back the
stuff in column C.

I used $a$1:$a$100, $b1:$b$100, and $c$1:$c$100 to hold that table (on
OtherSheet).

I'm gonna stick with a 100 rows, but you can make it as large as you want--but
not the whole column:

In the table:
Company name is in column A.
First name is in column E.
Last name is in column F.
And the data you want to bring back is in column B(??)
(difficult for me to see, but you can change it to what you need)

On your input sheet:
company name is in column C.
first name is in column X (you didn't say, or I missed it)
lastname is in column Y (You didn't say, or did I miss it again)

So that formula (all one cell again becomes something like:

=index(othersheet!$b$1:$b$100,
match(1,(c2=othersheet!$a$1:$a$100)
*(x2=othersheet!$e$1:$e$100)
*(y2=othersheet!$F$1:$f$100),0))


Remember...

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.



hgopp99 wrote:

Can you explain, in more detail, what the $A, $B, and $C represent? Also
what did (one cell) mean? I did not understand your reply.

I am trying to get the date, which is in the second column of one sheet to
the first column of another. The company, which is one field they have in
common is in the third column of the first sheet and the first column of the
sheet that I am trying to retreive my data. That is why I was originally
using:
=INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).

--
hgopp99

"Dave Peterson" wrote:

You can use this kind of syntax:

=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.

hgopp99 wrote:

I am trying to retreive a date from one worksheet and place it on another.
Most often, I can match on company name, but there are times when the company
name is not unique and I have to use first/last name. The following is an
example of data on the worksheet I am trying to retreive from:


Acutech 12/01/05 N Ms. Linda Dendy
Adams Simpson LLP 12/01/05 N Mr. Jesse Evans
Adams Simpson LLP 01/01/06 N Ms. Deborah Hembree

I am using the following: =INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).
This will work and find the match, pick the date (from the second column),
but in the case where the company is Adams Simpson LLP, it will always choose
the 12/01/05 date. I need to find both. So I would need to match on the
first and last name if there is more than one entry of the company.

Can this be done (simply)? Thank you.

--
hgopp99
--
hgopp99

--

Dave Peterson


--

Dave Peterson

Glad you got it working.

hgopp99 wrote:

Thank you for your patience. I now better understand the INDEX/MATCH array.
--
hgopp99

"hgopp99" wrote:

Thank you for the breakdown. I haven't tried it yet, but will before the
evening is over.
--
hgopp99


"Dave Peterson" wrote:

This formula would wrap in the newsgroup post. So I split it into two lines:

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

But when you use it, it would go into one cell--not two separate cells. (That
was the easy part <bg.)

Say I want to do essentially a =vlookup() based on two different cells. My
table has the key values in column A and column B (and it's on a worksheet named
OtherSheet.

And I want to bring back the value from column C of othersheet.

So I put the value to match othersheet column A in A2 of my current worksheet.

I put the value to match column B in B2 of my current worksheet.

Then that formula will look through all of othersheet and find the first row
where both column A and column B match my input. Then it'll bring back the
stuff in column C.

I used $a$1:$a$100, $b1:$b$100, and $c$1:$c$100 to hold that table (on
OtherSheet).

I'm gonna stick with a 100 rows, but you can make it as large as you want--but
not the whole column:

In the table:
Company name is in column A.
First name is in column E.
Last name is in column F.
And the data you want to bring back is in column B(??)
(difficult for me to see, but you can change it to what you need)

On your input sheet:
company name is in column C.
first name is in column X (you didn't say, or I missed it)
lastname is in column Y (You didn't say, or did I miss it again)

So that formula (all one cell again becomes something like:

=index(othersheet!$b$1:$b$100,
match(1,(c2=othersheet!$a$1:$a$100)
*(x2=othersheet!$e$1:$e$100)
*(y2=othersheet!$F$1:$f$100),0))


Remember...

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.



hgopp99 wrote:

Can you explain, in more detail, what the $A, $B, and $C represent? Also
what did (one cell) mean? I did not understand your reply.

I am trying to get the date, which is in the second column of one sheet to
the first column of another. The company, which is one field they have in
common is in the third column of the first sheet and the first column of the
sheet that I am trying to retreive my data. That is why I was originally
using:
=INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).

--
hgopp99

"Dave Peterson" wrote:

You can use this kind of syntax:

=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.

hgopp99 wrote:

I am trying to retreive a date from one worksheet and place it on another.
Most often, I can match on company name, but there are times when the company
name is not unique and I have to use first/last name. The following is an
example of data on the worksheet I am trying to retreive from:


Acutech 12/01/05 N Ms. Linda Dendy
Adams Simpson LLP 12/01/05 N Mr. Jesse Evans
Adams Simpson LLP 01/01/06 N Ms. Deborah Hembree

I am using the following: =INDEX(Misc!$A:$C,MATCH(C38,Misc!$A:$A,0),2).
This will work and find the match, pick the date (from the second column),
but in the case where the company is Adams Simpson LLP, it will always choose
the 12/01/05 date. I need to find both. So I would need to match on the
first and last name if there is more than one entry of the company.

Can this be done (simply)? Thank you.

--
hgopp99
--
hgopp99

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson