AppId is over the quota
VLOOKUP left
If Lookup Value is in Cell C1, Lookup Column is B and Result Column is A, then the “VLOOKUP left” formula will be =OFFSET(B1,MATCH(C1,B:B,0)-1,-1)
Last Matching value
If Lookup Value is in Cell C1, Lookup Column is B and Result Column is A, this array formula will result the last matching value: =OFFSET(B1,MAX(IF(C1=B:B,ROW(B:B)))-1,-1). This formula should be entering as an array formula (Click the formula bar and press Control+Shift+Enter). If you are using Excel 2003, specify the range like B1:B20, instead of entire column B:B.
Intermediate Matching values
If Lookup Value is in Cell C1, Counting of the lookup value is in cell D1, Lookup Column is B and result Column is A, this array formula will result the intermediate matching value: =OFFSET(B1,SMALL(IF(C1=B:B,ROW(B:B)),D1)-1,-1). This formula should be entering as an array formula (Click the formula bar and press Control+Shift+Enter). If you are using Excel 2003, specify the range like B1:B20, instead of entire column B:B.
No comments:
Post a Comment