|
VLookup with 2 criteria
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 |
VLookup with 2 criteria
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 he 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 |
VLookup with 2 criteria
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" wrote: 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 he 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 |
VLookup with 2 criteria
=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???? 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 the formula and let me know where you think it went wrong. Thanks, "Dave Peterson" wrote: 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 he 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 -- Dave Peterson |
VLookup with 2 criteria or Index/match?
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'!D2:D101,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'!D2:D101,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 Im very confused. Please help. "Dave Peterson" wrote: =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???? 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 the formula and let me know where you think it went wrong. Thanks, "Dave Peterson" wrote: 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 he 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 -- Dave Peterson |
VLookup with 2 criteria or Index/match?
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.) catts22 wrote: 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'!D2:D101,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'!D2:D101,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 Im very confused. Please help. "Dave Peterson" wrote: =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???? 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 the formula and let me know where you think it went wrong. Thanks, "Dave Peterson" wrote: 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 he 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 -- Dave Peterson -- Dave Peterson |
VLookup with 2 criteria or Index/match?
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. On Aug 25, 9:34*am, catts22 wrote: 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'!D2:D101,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'!D2:D101,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. "Dave Peterson" wrote: =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 the formula and let me know where you think it went wrong. Thanks, "Dave Peterson" wrote: 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 if there 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 changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he 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 -- Dave Peterson |
| All times are GMT +1. The time now is 10:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com