Offset and vlookup not linking to other sheet.

[Abhinav Shori]

=IF(ISNA(VLOOKUP("name", OFFSET('sheet2'!$A$2, (ROW()-1)*10, 0, 10, 4), 3, FALSE))= TRUE, "", VLOOKUP("name", OFFSET('sheet2'!$A$2, (ROW()-1)*10, 0, 10, 4), 3))

this is the formula that I have. It is a dynamic range formula. I tested it on the same sheet it works. However, if my table is on "sheet2" it is not linking with the above formula.

How could I link this function to "sheet2", please help. if you need any more information please let me know, thanks.

 

[Somendra Misra]

Hi Abhinav,

Can you share your file over here? and there is no required to put =TRUE in formula.
Regards,

 

[Khalid NGO]

Hellow Abhinav,
What error you are receiving?

 

[Abhinav Shori]

Khalid: No error, it seems that the sheets are just not linking.

Hi Abhinav,

Can you share your file over here? and there is no required to put =TRUE in formula.
Regards,

Somendra: there are 2 sheets sheet 1 and sheet 2. so in sheet 1, cell A1, i would like to catch the 3rd column for a specific name in sheet 2.


example:

there is a table in sheet 2 like:

a b c

1 abhinav 12 16

2 somendra 9 12

I put the above formula in sheet 1

=IF(ISNA(VLOOKUP("abhinav", OFFSET(Sheet2!$A$1, (ROW()-1)*10, 0, 10, 4), 3, FALSE))= TRUE, "", VLOOKUP("abhinav", OFFSET(Sheet2!$A$1, (ROW()-1)*10, 0, 10, 4), 3))

the answer should be 16 as it should catch the 3rd column for the corresponding name. right?

 

[Somendra Misra]

If your table is fixed, than why are you putting OFFSET in your formula?

Regards,

 

[Abhinav Shori]

If your table is fixed, than why are you putting OFFSET in your formula?

Regards,

It's not fixed, the range will be dynamic with a jump of 10 and the table will be 10X4.

 

[Somendra Misra]

Upload a sample file, with the same structure.

Regards,

 

[NARAYANK991]

Hi Abhinav ,

The formula works correctly , at least it gives the output you have posted.

Narayan