Lookup a Value Based on a Single Criteria in a Filtered List

Unfiltered List

Looking Up a Value Based on a Single Criteria - Unfiltered List

Filtered List

Looking Up a Value Based on a Single Criteria - 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