OFFSET along with INDEX

[vijay.vizzu]

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

 

[Hui]

I'd try:

=INDEX(OFFSET(INDIRECT("J"&MATCH(V7,J11:J171,0)+10),0,0, COUNTIF(J11:J171,V7),1), COUNTIF(J11:J171,V7), 0)


Offset requires a range, not text

 

[vijay.vizzu]

Thank you so much HUI, it works fine, but please explain why you have used indirect in this formula. If it is possible please share information about using INDIRECT formula.

 

[vijay.vizzu]

Can it is possible to write the macro code to do this. I have tried but can't able to acheive the target, can you please share

 

[Hui]

Offset requires the first parameter to be a Range

When you hard code say J100 excel recognises that as a range and deals with it appropriately

When you enter "J"&MATCH(V7,J11:J171,0)+10, even though it may look like J100 the " 's tell Excel that it is a Text String

Indirect converts text strings to Ranges

 

[vijay.vizzu]

Dear HUI


Thank you so much clear explanation regarding OFFSET & INDIRECT. I will keep it in mind in further.

 

[Hui]

Vijay


Keep in mind that some functions return Range References by default including:

Address, Offset, Indirect & Index

 

[vijay.vizzu]

Thank you Hui to share your experience with us. I will keep it in my mind