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

[Guest]

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

 

[Bob Phillips]

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)

 

[Rob van Gelder]

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

 

[Guest]

it didn't work for me

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)

 

[Dave Peterson]

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$100=B1),0))

(one cell)

 

[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)&Sheet2!$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]

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>

 

[Bob Phillips]

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

Regards

Bob

 

[Bob Phillips]

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