help with vlookup with multiple criteria

[jcon2112]

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

 

[jcon2112]

Ok, i realize that looks pretty bad here are better examples :

Spreadsheet 1


Spreadsheet 2

 

[Peggy Shepard]

Hi jcon2112,

In Sheet2, cell D2:

=INDIRECT("Sheet1!"&ADDRESS(MATCH(A2,Sheet1!B:B,0),MATCH(B2,Sheet1!$1:$1,0)))

Peggy

 

[jcon2112]

Peggy! Thank you so much for your help.
How would this look if it were 2 workbooks? i mistakenly called them
worksheets.

 

[pshepard]

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\Desktop\[Book1.xlsx]Sheet1'!B:B,0),MATCH(B2,'C:\Users\Peggy\Desktop\[Book1.xlsx]Sheet1'!$1:$1,0))