VLookup with 2 criteria

[Meredith]

Hi,

I need a VLOOKUP which can match 2 criteria.

I have 2 spreadsheets which each contain: Client No. and Engagement
Description. They are the same on both spreadsheets, however, I am doing the
VLOOKUP to find the Budget for that specific Client No. and Engagement
Description.

Is there a way to get the VLOOKUP or any other formula to work?

Thank you

 

[Dave Peterson]

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in xl2007.

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))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Meredith]

Hi Dave,

I tried to enter this

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,MATCH(1,A5='[WIP -
August 19.xls]19-Aug-2009'!$E$2:$E$3873)*(B5='[WIP - August
19.xls]19-Aug-2009'!$G$2:$G$3873),0) using the ctrl+shift+enter functions.

It came back with an #NA error. Can you please try to diagnose the formula
and let me know where you think it went wrong.

Thanks,

 

[Dave Peterson]

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,
MATCH(1,A5='[WIP - August 19.xls]19-Aug-2009'!$E$2:$E$3873)
*(B5='[WIP - August 19.xls]19-Aug-2009'!$G$2:$G$3873),0)

I'm gonna guess that there is no match between the value in A5 and E2:E3873 and
at the same time between the value in B5 and G2:G3873.

Just like =vlookup() or =match() will return an error.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
(And it'll apply to =match(), too.)

If that doesn't help, what are you matching on--simple text, whole numbers,
times, dates, fractions, long strings????

Hi Dave,

I tried to enter this

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,MATCH(1,A5='[WIP -
August 19.xls]19-Aug-2009'!$E$2:$E$3873)*(B5='[WIP - August
19.xls]19-Aug-2009'!$G$2:$G$3873),0) using the ctrl+shift+enter functions.

It came back with an #NA error. Can you please try to diagnose the formula
and let me know where you think it went wrong.

Thanks,

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in xl2007.

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))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[catts22]

Hi - I have a similar question...

I have 3 columns on Sheet 1 and need to have the result in the fourth column
based on information on Sheet 2.

Sheet 1
A B C D
1 State City Address Company
2 AL Birming ALL
3 NC All ALL
4 NY New York 123 X St
5 NY Long City 999 A St


A B C D
1 State City Address Company
2 AL Birming ALL Yellow
3 NC All ALL Yellow
4 NY New York 123 X St Red
5 NY Long City 999 A St Blue
6 CT ALL ALL Red
7 GA ATLANTA 5TH St Red


In D2 on Sheet 1 - I tried this formula with the idea that I need the
company name shown on column D of Sheet 2 if Column A,B,C match on both sheets

=INDEX('Sheet 2'!D2101,MATCH(1,(a2='Sheet 2'!A2:A101)*(b2='Sheet
2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))

I keep getting # N/A -

I read that you must use control shift enter to get curly brackets – when I
did this I got {=INDEX('Sheet 2'!D2101,MATCH(1,(a2='Sheet
2'!A2:A101)*(b2='Sheet 2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))} - the result
was still #NA

Your formula example has [ ] brackets, so now I’m very confused.

Please help.

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,
MATCH(1,A5='[WIP - August 19.xls]19-Aug-2009'!$E$2:$E$3873)
*(B5='[WIP - August 19.xls]19-Aug-2009'!$G$2:$G$3873),0)

I'm gonna guess that there is no match between the value in A5 and E2:E3873 and
at the same time between the value in B5 and G2:G3873.

Just like =vlookup() or =match() will return an error.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
(And it'll apply to =match(), too.)

If that doesn't help, what are you matching on--simple text, whole numbers,
times, dates, fractions, long strings????

Hi Dave,

I tried to enter this

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,MATCH(1,A5='[WIP -
August 19.xls]19-Aug-2009'!$E$2:$E$3873)*(B5='[WIP - August
19.xls]19-Aug-2009'!$G$2:$G$3873),0) using the ctrl+shift+enter functions.

It came back with an #NA error. Can you please try to diagnose the formula
and let me know where you think it went wrong.

Thanks,

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in xl2007.

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))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Meredith wrote:

Hi,

I need a VLOOKUP which can match 2 criteria.

I have 2 spreadsheets which each contain: Client No. and Engagement
Description. They are the same on both spreadsheets, however, I am doing the
VLOOKUP to find the Budget for that specific Client No. and Engagement
Description.

Is there a way to get the VLOOKUP or any other formula to work?

Thank you

 

[Dave Peterson]

The original formula that I suggested didn't have those []'s. But Meredith was
trying to retrieve data from a worksheet in a different workbook. The name of
that (already opened) workbook is in those square brackets []'s.

And I'm gonna guess that you are suffering from the same problem that Meredith
has. Your data doesn't really match. There is no single row in Sheet2 that
matches A2 in A2:A101, b2 in B2:B101 and C2 in C2:C101.

Try an experiment.

Insert a new row (say row 10) in that Sheet2 (the table sheet).

Put these values in A10, B10, C10:
asdf
qwer
zxcv

Then put those same values in the sheet with the formula (in A2:C2). I bet your
formula (still array entered) works perfectly.

So your job will be to find out why those values don't match--even when they
look like they match to you.

Look for leading/trailing spaces. Typos... and check Debra's site, too:

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
(And it'll apply to =match(), too.)

Hi - I have a similar question...

I have 3 columns on Sheet 1 and need to have the result in the fourth column
based on information on Sheet 2.

Sheet 1
A B C D
1 State City Address Company
2 AL Birming ALL
3 NC All ALL
4 NY New York 123 X St
5 NY Long City 999 A St

A B C D
1 State City Address Company
2 AL Birming ALL Yellow
3 NC All ALL Yellow
4 NY New York 123 X St Red
5 NY Long City 999 A St Blue
6 CT ALL ALL Red
7 GA ATLANTA 5TH St Red

In D2 on Sheet 1 - I tried this formula with the idea that I need the
company name shown on column D of Sheet 2 if Column A,B,C match on both sheets

=INDEX('Sheet 2'!D2101,MATCH(1,(a2='Sheet 2'!A2:A101)*(b2='Sheet
2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))

I keep getting # N/A -

I read that you must use control shift enter to get curly brackets – when I
did this I got {=INDEX('Sheet 2'!D2101,MATCH(1,(a2='Sheet
2'!A2:A101)*(b2='Sheet 2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))} - the result
was still #NA

Your formula example has [ ] brackets, so now I’m very confused.

Please help.

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,
MATCH(1,A5='[WIP - August 19.xls]19-Aug-2009'!$E$2:$E$3873)
*(B5='[WIP - August 19.xls]19-Aug-2009'!$G$2:$G$3873),0)

I'm gonna guess that there is no match between the value in A5 and E2:E3873 and
at the same time between the value in B5 and G2:G3873.

Just like =vlookup() or =match() will return an error.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
(And it'll apply to =match(), too.)

If that doesn't help, what are you matching on--simple text, whole numbers,
times, dates, fractions, long strings????

Hi Dave,

I tried to enter this

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,MATCH(1,A5='[WIP -
August 19.xls]19-Aug-2009'!$E$2:$E$3873)*(B5='[WIP - August
19.xls]19-Aug-2009'!$G$2:$G$3873),0) using the ctrl+shift+enter functions.

It came back with an #NA error. Can you please try to diagnose the formula
and let me know where you think it went wrong.

Thanks,

:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in xl2007.

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))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Meredith wrote:

Hi,

I need a VLOOKUP which can match 2 criteria.

I have 2 spreadsheets which each contain: Client No. and Engagement
Description. They are the same on both spreadsheets, however, I am doing the
VLOOKUP to find the Budget for that specific Client No. and Engagement
Description.

Is there a way to get the VLOOKUP or any other formula to work?

Thank you

 

[Ben Webman]

Vlookup can be used and you dont need the array for that (but will
need it for the match). I like to lock down the Vlookup formula ($A2:A
$99999) You could run into a problem when you if u have duplicate
data. If possible give these unique id's if these are order numbers -
1,2,3,4 (or concatenate the known unique information that is shared
and lookup that value). You can nest the lookup inside of IF
statements. Your selection of function is somewhat dependent how the
sheets are structured and what information you want.

Hi - I have a similar question...

I have 3 columns on Sheet 1 and need to have the result in the fourth column
based on information on Sheet 2.

Sheet 1
        A       B       C       D
1       State   City    Address Company
2       AL      Birming     ALL
3       NC      All     ALL
4       NY      New York        123 X St
5       NY      Long City       999 A St

        A       B       C       D
1       State   City    Address Company
2       AL      Birming ALL     Yellow
3       NC      All     ALL     Yellow
4       NY      New York        123 X St        Red
5       NY      Long City       999 A St        Blue
6       CT      ALL     ALL     Red
7       GA      ATLANTA 5TH St  Red

In D2 on Sheet 1 -  I tried this formula with the idea that I need the
company name shown on column D of Sheet 2 if Column A,B,Cmatchon both sheets

=INDEX('Sheet 2'!D2101,MATCH(1,(a2='Sheet 2'!A2:A101)*(b2='Sheet
2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))

I keep getting # N/A  -

I read that you must use control shift enter to get curly brackets – when I
did this I got  {=INDEX('Sheet 2'!D2101,MATCH(1,(a2='Sheet
2'!A2:A101)*(b2='Sheet 2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))}  - the result
was still #NA

Your formula example has [ ] brackets, so now I’m very confused.  

Please help.

=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,
     MATCH(1,A5='[WIP - August 19.xls]19-Aug-2009'!$E$2:$E$3873)
            *(B5='[WIP - August 19.xls]19-Aug-2009'!$G$2:$G$3873),0)

I'm gonna guess that there is nomatchbetween the value in A5 and E2:E3873 and
at the same time between the value in B5 and G2:G3873.

Just like =vlookup() or =match() will return an error.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
(And it'll apply to =match(), too.)

If that doesn't help, what are you matching on--simple text, whole numbers,
times, dates, fractions, long strings????

Meredith wrote:

Hi Dave,
I tried to enter this
=INDEX('[WIP - August 19.xls]19-Aug-2009'!$N$2:$N$3873,MATCH(1,A5='[WIP -
August 19.xls]19-Aug-2009'!$E$2:$E$3873)*(B5='[WIP - August
19.xls]19-Aug-2009'!$G$2:$G$3873),0) using the ctrl+shift+enter functions.
It came back with an #NA error.  Can you please try to diagnose theformula
and let me know where you think it went wrong.
Thanks,
:
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
   match(1,(a2=othersheet!$a$1:$a$100)
          *(b2=othersheet!$b$1:$b$100),0))
(all in 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 tomatch--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet)matchA2 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))
============
If there is only onematchand you're bringing back a number (or 0 ifthere is
nomatchfor all the criteria), you can use:
=sumproduct(--(othersheet!a1:a10=a1),
            --(othersheet!b1:b10=b1),
              (othersheet!c1:c10))
Adjust the ranges tomatch--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers.  The -- stuff changestrues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Meredith wrote:
Hi,
I need a VLOOKUP which canmatch2 criteria.
I have 2 spreadsheets which each contain: Client No. and Engagement
Description.  They are the same on both spreadsheets, however, I am doing the
VLOOKUP to find the Budget for that specific Client No. and Engagement
Description.
Is there a way to get the VLOOKUP or any other formula to work?
Thank you

--

Dave Peterson