Lookup Based on a Single Criteria in a Filtered List
Unfiltered List
Filtered List
The following formulas search within a filtered list for a value in B3:B8 that equals the value in E2, and return the corresponding value from C3:C8...
=INDEX(C3:C8,MATCH(1,IF(SUBTOTAL(3,OFFSET(B3:B8,ROW(B3:B8)-ROW(B3),0,1))>0,IF(B3:B8=E2,1)),0))
or
=VLOOKUP(E2,IF(SUBTOTAL(3,OFFSET(B3:B8,ROW(B3:B8)-ROW(B3),0,1))>0,B3:C8),2,0)
Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will autmatically place curly braces {...} around the formula.
Based on the sample data, the formulas return 275.
Sample Workbook: Download