Is there a way to do a vlookup to look up two columns versus one?
 

I basically have Data set A with a date column and an id column and other
data. I also have Data set B that contains the same date and id column and a
third column. I want to be able to look at the data in Data set B and if the
date and id match the date and id in Data set A, then put the information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle

Hi Joselle,

Try this

assuming the date is in A1, id in B1, and the other data is A, B and C on
Sheet 2,

=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1: $A$100&Sheet2!$B$1:$B$100,
0))

this is an arrya form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jpd" wrote in message
...
I basically have Data set A with a date column and an id column and other
data. I also have Data set B that contains the same date and id column
and a
third column. I want to be able to look at the data in Data set B and if
the
date and id match the date and id in Data set A, then put the information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle

Hey - I didn't know you could concatenate ranges for MATCH - very cool.
That's a very handy tip - thanks Bob.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Hi Joselle,

Try this

assuming the date is in A1, id in B1, and the other data is A, B and C on
Sheet 2,

=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1: $A$100&Sheet2!$B$1:$B$100,
0))

this is an arrya form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jpd" wrote in message
...
I basically have Data set A with a date column and an id column and other
data. I also have Data set B that contains the same date and id column
and a
third column. I want to be able to look at the data in Data set B and if
the
date and id match the date and id in Data set A, then put the information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle

it didn't work for me :(

"Bob Phillips" wrote:

Hi Joselle,

Try this

assuming the date is in A1, id in B1, and the other data is A, B and C on
Sheet 2,

=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1: $A$100&Sheet2!$B$1:$B$100,
0))

this is an arrya form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jpd" wrote in message
...
I basically have Data set A with a date column and an id column and other
data. I also have Data set B that contains the same date and id column
and a
third column. I want to be able to look at the data in Data set B and if
the
date and id match the date and id in Data set A, then put the information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle

Did you hit ctrl-shift-enter instead of just enter?

And another option (still hit ctrl-shift-enter).

=INDEX(Sheet2!$C$1:$C$100,
MATCH(1,(Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$10 0=B1),0))

(one cell)

jpd wrote:

it didn't work for me :(

"Bob Phillips" wrote:

Hi Joselle,

Try this

assuming the date is in A1, id in B1, and the other data is A, B and C on
Sheet 2,

=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1: $A$100&Sheet2!$B$1:$B$100,
0))

this is an arrya form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jpd" wrote in message
...
I basically have Data set A with a date column and an id column and other
data. I also have Data set B that contains the same date and id column
and a
third column. I want to be able to look at the data in Data set B and if
the
date and id match the date and id in Data set A, then put the information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle




--

Dave Peterson

Just something to watch out for:

It might be better to toss a character that won't appear in your data to
separate those cells in the concatenation:

=INDEX(Sheet2!$C$1:$C$100,
MATCH(A1&CHAR(1)&B1,Sheet2!$A$1:$A$100&CHAR(1)&She et2!$B$1:$B$100,0))

If you have:

A B C
RobVan Gelder 1
Rob VanGelder 2
RobVanGelder 3
RobVanGelder 4

You might not get the value you really want returned.




Rob van Gelder wrote:

Hey - I didn't know you could concatenate ranges for MATCH - very cool.
That's a very handy tip - thanks Bob.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Bob Phillips" wrote in message
...
Hi Joselle,

Try this

assuming the date is in A1, id in B1, and the other data is A, B and C on
Sheet 2,

=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1: $A$100&Sheet2!$B$1:$B$100,
0))

this is an arrya form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jpd" wrote in message
...
I basically have Data set A with a date column and an id column and other
data. I also have Data set B that contains the same date and id column
and a
third column. I want to be able to look at the data in Data set B and if
the
date and id match the date and id in Data set A, then put the information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle



--

Dave Peterson

I understand... and thanks for your comment.

It's odd that you use CHAR(1). It's exactly what I use too!
great minds... <g

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Dave Peterson" wrote in message
...
Just something to watch out for:

It might be better to toss a character that won't appear in your data to
separate those cells in the concatenation:

=INDEX(Sheet2!$C$1:$C$100,
MATCH(A1&CHAR(1)&B1,Sheet2!$A$1:$A$100&CHAR(1)&She et2!$B$1:$B$100,0))

If you have:

A B C
RobVan Gelder 1
Rob VanGelder 2
RobVanGelder 3
RobVanGelder 4

You might not get the value you really want returned.




Rob van Gelder wrote:

Hey - I didn't know you could concatenate ranges for MATCH - very cool.
That's a very handy tip - thanks Bob.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Bob Phillips" wrote in message
...
Hi Joselle,

Try this

assuming the date is in A1, id in B1, and the other data is A, B and C
on
Sheet 2,

=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1: $A$100&Sheet2!$B$1:$B$100,
0))

this is an arrya form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jpd" wrote in message
...
I basically have Data set A with a date column and an id column and
other
data. I also have Data set B that contains the same date and id
column
and a
third column. I want to be able to look at the data in Data set B and
if
the
date and id match the date and id in Data set A, then put the
information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle



--

Dave Peterson

That's a good idea, and is very sound advice. But then we have come to
expect that :-)

Regards

Bob


"Dave Peterson" wrote in message
...
Just something to watch out for:

It might be better to toss a character that won't appear in your data to
separate those cells in the concatenation:

=INDEX(Sheet2!$C$1:$C$100,
MATCH(A1&CHAR(1)&B1,Sheet2!$A$1:$A$100&CHAR(1)&She et2!$B$1:$B$100,0))

If you have:

A B C
RobVan Gelder 1
Rob VanGelder 2
RobVanGelder 3
RobVanGelder 4

You might not get the value you really want returned.




Rob van Gelder wrote:

Hey - I didn't know you could concatenate ranges for MATCH - very cool.
That's a very handy tip - thanks Bob.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

"Bob Phillips" wrote in message
...
Hi Joselle,

Try this

assuming the date is in A1, id in B1, and the other data is A, B and C
on
Sheet 2,


=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1: $A$100&Sheet2!$B$1:$B$100,
0))

this is an arrya form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jpd" wrote in message
...
I basically have Data set A with a date column and an id column and
other
data. I also have Data set B that contains the same date and id
column
and a
third column. I want to be able to look at the data in Data set B
and if
the
date and id match the date and id in Data set A, then put the
information
from that third column into Data set A. Can anyone help?
Thanks!
Joselle



--

Dave Peterson

Hi Rob,

It's my pleasure mate. Learning is great isn't it. I got a lot of fun and
thought out of Jamie's (Collins) comments on Public variables the other day.

Bob


"Rob van Gelder" wrote in message
...
Hey - I didn't know you could concatenate ranges for MATCH - very cool.
That's a very handy tip - thanks Bob.

--
Rob van Gelder - http://www.vangelder.co.nz/excel