|
|
| Lookup
a Value in a Filtered List |
Unfiltered
List
Filtered
List
The following formulas search within a filtered list for a value in
C3:C8 that equals the value in F2, and return the corresponding value
from D3:D8...
=INDEX(D3:D8,MATCH(1,IF(SUBTOTAL(3,OFFSET(C3:C8,ROW(C3:C8)-ROW(C3),0,1)),IF(C3:C8=F2,1)),0))
or
=VLOOKUP(F2,IF(SUBTOTAL(3,OFFSET(C3:C8,ROW(C3:C8)-ROW(C3),0,1)),C3:D8),2,0)
| Note
that these are array formulas, which need
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formulas return 275. Sample
Workbook |
|
|
