Filtering multiple criteria to get specific data

[lawandgrace]

I have the following source data on Sheet4:

Column M Column N Column O
1A N632VA 4
1A N526VA 4
1A N624VA 4
2A N632VA 6
1D N526VA 8
3D N624VA 4


On Sheet1 is where I want to show the results.

On Sheet1, I have a dropdown for the data shown in Column N (from Sheet4).
In addition, the following is true:

Cell C9=1, Cell D9=2, Cell E9=3, Cell F9=4 and so on up to 8.
Cell B10=D, Cell B11=A.

These cells on Sheet1 correspond to Column M on Sheet4; i.e., Cell C9 and
Cell B11 ("1" and "A" respectively) correspond to "1A" on Sheet4, Column M.

If I select from the dropdown on Sheet1 "N632VA", I want Cell C11 to show
"4" (from Column O on Sheet4).

Thanks!

 

[Gary''s Student]

You can use SUMPRODUCT() to get the row number and then use OFFSET() to get
the actual data. For example:

=SUMPRODUCT((M1:M100="1A")*(N1:N100="N624VA")*(ROW(1:100)))
shows 3 (for the third row)

and

=OFFSET($O$1,SUMPRODUCT((M1:M100="1A")*(N1:N100="N624VA")*(ROW(1:100)))-1,0)
will display the value 4 in that row.