Excel lookup Multiple Criteria Index Match Formula Issue 2

[Randy11]

Am performing a lookup in Excel 2007 with two pieces of criteria. If both lookup criteria (Home & Code) are met, info in named range(Location) to be returned. Problem is it is only returning the result if it is the 1st row containing the Home criteria that criteria matches. Assistance appreciated.

If I lookup 11 & Drone 320 is answer
If I lookup 11 & Colter N/A# is answer

Formula used is:
=INDEX(Location,MATCH($A2,IF(Code=C$1,Home),0))
Note: this lookup is on another worksheet. A2 has the Home number C1 has the Code that form the criteria for the lookup.

Sample Data (Each Column is a Named Range. Column A is "Home", B is "Location", C is Code.

Home Location Code
11 320 Drone
11 600 Colter
11 780 Knookums
11 790 NonDruids
13 830 Drone
13 1150 Colter
13 2250 Knookums
13 2610 NonDruids
15 9280 Drone
15 10320 Colter
15 10781 Knookums
15 10790 NonDruids

 

[SkipVought]

hi,

Using Named Ranges of your table...
[tt]
=INDEX(Location,MATCH(G3,Home,0)-1+MATCH(H3,OFFSET(Home,MATCH(G3,Home,0)-1,2,COUNTIF(Home,G3),1),0),1)
[/tt]
where G3 contains the Home criteria and H3 the Code criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

This worked perfect, thanks Skip!