| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup with multiple criteria
Hi Everyone,
i have a problem i'm hoping to get solved here. I have 2 spreadsheets with data and i need to do some type of vlookup/hlookup formula. Spreadsheet 1 has data like this: Reg # A101 A102 A103 A104 A105 A106 A107 USA123 15 5 0 15 0 0 0 USA124 0 0 0 0 0 0 0 USA125 13 1 2 0 0 0 0 USA126 0 0 0 0 0 0 0 USA127 4 11 1 0 0 0 0 USA128 1 0 0 0 8 6 0 USA129 0 0 0 0 0 0 0 USA130 12 15 2 6 27 3 11 USA131 4 0 3 0 0 0 0 USA132 8 0 0 0 18 4 0 Spreadsheet 2: Area Name Model Quantity USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A104 1 USA124 A104 1 USA124 A104 1 Now what i need to do in spreadsheet 2, in column D, bring back the resulting value of a lookup from spreadsheet one based on if B2 (spreadsheet 2, "model" is A101, go to spreadsheet 1 and find the region (usa123) and bring back the qty of A101 in region USA123 (should be 15). Jeez i hope that makes sense to someone. TIA James |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with vlookup with multiple criteria
Ok, i realize that looks pretty bad here are better examples :
Spreadsheet 1 Spreadsheet 2 Now what i need to do in spreadsheet 2, in column D, bring back the resulting value of a lookup from spreadsheet one based on if B2 (spreadsheet 2, "model" is A101, go to spreadsheet 1 and find the region (usa123) and bring back the qty of A101 in region USA123 (should be 15). Jeez i hope that makes sense to someone. TIA James |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with vlookup with multiple criteria
Hi jcon2112,
In Sheet2, cell D2: =INDIRECT("Sheet1!"&ADDRESS(MATCH(A2,Sheet1!B:B,0) ,MATCH(B2,Sheet1!$1:$1,0))) Peggy "jcon2112" wrote in message ... Hi Everyone, i have a problem i'm hoping to get solved here. I have 2 spreadsheets with data and i need to do some type of vlookup/hlookup formula. Spreadsheet 1 has data like this: Reg # A101 A102 A103 A104 A105 A106 A107 USA123 15 5 0 15 0 0 0 USA124 0 0 0 0 0 0 0 USA125 13 1 2 0 0 0 0 USA126 0 0 0 0 0 0 0 USA127 4 11 1 0 0 0 0 USA128 1 0 0 0 8 6 0 USA129 0 0 0 0 0 0 0 USA130 12 15 2 6 27 3 11 USA131 4 0 3 0 0 0 0 USA132 8 0 0 0 18 4 0 Spreadsheet 2: Area Name Model Quantity USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A104 1 USA124 A104 1 USA124 A104 1 Now what i need to do in spreadsheet 2, in column D, bring back the resulting value of a lookup from spreadsheet one based on if B2 (spreadsheet 2, "model" is A101, go to spreadsheet 1 and find the region (usa123) and bring back the qty of A101 in region USA123 (should be 15). Jeez i hope that makes sense to someone. TIA James |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with vlookup with multiple criteria
Peggy! Thank you so much for your help.
How would this look if it were 2 workbooks? i mistakenly called them worksheets. "Peggy Shepard" wrote in message ... Hi jcon2112, In Sheet2, cell D2: =INDIRECT("Sheet1!"&ADDRESS(MATCH(A2,Sheet1!B:B,0) ,MATCH(B2,Sheet1!$1:$1,0))) Peggy "jcon2112" wrote in message ... Hi Everyone, i have a problem i'm hoping to get solved here. I have 2 spreadsheets with data and i need to do some type of vlookup/hlookup formula. Spreadsheet 1 has data like this: Reg # A101 A102 A103 A104 A105 A106 A107 USA123 15 5 0 15 0 0 0 USA124 0 0 0 0 0 0 0 USA125 13 1 2 0 0 0 0 USA126 0 0 0 0 0 0 0 USA127 4 11 1 0 0 0 0 USA128 1 0 0 0 8 6 0 USA129 0 0 0 0 0 0 0 USA130 12 15 2 6 27 3 11 USA131 4 0 3 0 0 0 0 USA132 8 0 0 0 18 4 0 Spreadsheet 2: Area Name Model Quantity USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A104 1 USA124 A104 1 USA124 A104 1 Now what i need to do in spreadsheet 2, in column D, bring back the resulting value of a lookup from spreadsheet one based on if B2 (spreadsheet 2, "model" is A101, go to spreadsheet 1 and find the region (usa123) and bring back the qty of A101 in region USA123 (should be 15). Jeez i hope that makes sense to someone. TIA James |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help with vlookup with multiple criteria
Hi James,
Let me know how this works for you. The path is for my desktop on my computer - you will need to determine the path for your machine. Excel 2007 - =INDEX('C:\Users\Peggy\Desktop\[Book1.xlsx]Sheet1'!$A$1:$I$11,MATCH(A2,'C:\Users\Peggy\Deskto p\[Book1.xlsx]Sheet1'!B:B,0),MATCH(B2,'C:\Users\Peggy\Desktop\[Book1.xlsx]Sheet1'!$1:$1,0)) -- If this post helps click Yes --------------- Peggy Shepard "jcon2112" wrote: Peggy! Thank you so much for your help. How would this look if it were 2 workbooks? i mistakenly called them worksheets. "Peggy Shepard" wrote in message ... Hi jcon2112, In Sheet2, cell D2: =INDIRECT("Sheet1!"&ADDRESS(MATCH(A2,Sheet1!B:B,0) ,MATCH(B2,Sheet1!$1:$1,0))) Peggy "jcon2112" wrote in message ... Hi Everyone, i have a problem i'm hoping to get solved here. I have 2 spreadsheets with data and i need to do some type of vlookup/hlookup formula. Spreadsheet 1 has data like this: Reg # A101 A102 A103 A104 A105 A106 A107 USA123 15 5 0 15 0 0 0 USA124 0 0 0 0 0 0 0 USA125 13 1 2 0 0 0 0 USA126 0 0 0 0 0 0 0 USA127 4 11 1 0 0 0 0 USA128 1 0 0 0 8 6 0 USA129 0 0 0 0 0 0 0 USA130 12 15 2 6 27 3 11 USA131 4 0 3 0 0 0 0 USA132 8 0 0 0 18 4 0 Spreadsheet 2: Area Name Model Quantity USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA123 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A101 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A103 1 USA124 A104 1 USA124 A104 1 USA124 A104 1 Now what i need to do in spreadsheet 2, in column D, bring back the resulting value of a lookup from spreadsheet one based on if B2 (spreadsheet 2, "model" is A101, go to spreadsheet 1 and find the region (usa123) and bring back the qty of A101 in region USA123 (should be 15). Jeez i hope that makes sense to someone. TIA James |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup multiple criteria multiple occurrences sum values | Excel Worksheet Functions | |||
| Vlookup with Multiple criteria and multiple sheets | Excel Worksheet Functions | |||
| Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
| Vlookup for multiple criteria | Excel Worksheet Functions | |||
| Vlookup with multiple criteria | Excel Worksheet Functions | |||
Linear Mode
