multiple lookups and filtering
 

I have the following:

Sheet3=

Drop-down selection ______

1 2 3 4 5 -- up to 103
D 4 5 8 4 6 <--these are the formula results
A 5 4 6 6 4 <--these are the formula results

Sheet4=

location1 location2 location3
1A N632VA 5
1D N632VA 4
5A N632VA 4

Location 2 on Sheet4 has multiple options (I've only shown one). I have a
drop-down list on Sheet3 that I want to filter the data, which will in turn
effect the data pulled in by the formulas.

I want the formula on Sheet3 to look at the drop-down selection (in this
case N632VA), find the corresponding value on Sheet4 (location2), then find
the combination of the number above and the letter to the left (such as 1 and
A) and find that combination on Sheet4 (location1, "1A"), then return the
number in Location3.

I'm sorry if I'm not that clear. The formula I've been trying to use (and
doesn't work) is:

{=INDEX(Sheet4!$C$2:$C$2000,MATCH(IF(Sheet4!$A$2:$ A$2000=C$9&$B11,Sheet4!$C$2:$C$2000),IF(Sheet4!$B$ 2:$B$2000=$B$2,Sheet4!$O$2:$O$2000),0))}

The C$9&$B11 in the formula reflects the "1" and "A" on Sheet3 to match it
to the "1A" (location1) on Sheet4.

Thanks for any help you can provide.