vijay.vizzu
Member
Dear All,
Problem phonomena is below
1. My Lookup value in cell V7 : *F2482* (by using formula i.e ="*"&IF(LEFT(J11,1)="9",LEFT(J11,11),MID(J11,5,5))&"*" (Result is *F2482*)
2. Getting position of the lookup value in the range (in cell V8): ="J"&MATCH(V7,J11:J171,0)+10 (Result is J161)
3. Total number of repetitons of the lookup value (in cell V6): =COUNTIF(J11:J171,V7) (Result is 8)
4. Now i want to dynamically set the offset for lookup value start and end. So i tried below formula OFFSET(J161,0,0,8,1) (It became array values) around 8 items
5. Now i want last item (V6 value = 8) in that array. (8th item in that array)
6. Now i used index to get that value =INDEX(OFFSET(J161,0,0,8,1),8,0). Right answer, it working fine
But when i combined all those formulas in cell V4 like =INDEX(OFFSET("J"&MATCH(V7,J11:J171,0)+10,0,0,COUNTIF(J11:J171,V7),1),COUNTIF(J11:J171,V7),0)
It gives error and i can't able to combine the formulas
Please help
Problem phonomena is below
1. My Lookup value in cell V7 : *F2482* (by using formula i.e ="*"&IF(LEFT(J11,1)="9",LEFT(J11,11),MID(J11,5,5))&"*" (Result is *F2482*)
2. Getting position of the lookup value in the range (in cell V8): ="J"&MATCH(V7,J11:J171,0)+10 (Result is J161)
3. Total number of repetitons of the lookup value (in cell V6): =COUNTIF(J11:J171,V7) (Result is 8)
4. Now i want to dynamically set the offset for lookup value start and end. So i tried below formula OFFSET(J161,0,0,8,1) (It became array values) around 8 items
5. Now i want last item (V6 value = 8) in that array. (8th item in that array)
6. Now i used index to get that value =INDEX(OFFSET(J161,0,0,8,1),8,0). Right answer, it working fine
But when i combined all those formulas in cell V4 like =INDEX(OFFSET("J"&MATCH(V7,J11:J171,0)+10,0,0,COUNTIF(J11:J171,V7),1),COUNTIF(J11:J171,V7),0)
It gives error and i can't able to combine the formulas
Please help