# Offset index match

#### kumar lama

##### Board Regular
Hello,
My master workbook(master data sheet) Column A has S.No. But this S.No Has break In middle, like A3= "AAAA" then from A4 Start S.No
like A4 = 1, A5=2 ,A6=3, A7=4, A8=5 ,A9=6 ......And again A10= "BBBB" and start S. No. Like A11=1,A12=2,A13=3,A14=4, A15=5........
Basicly AAAA & BBBB is a group, when person join for AAAA group i have to insert Row in last of AAAA. same in BBBB & CCCC also
i have another workbook =Group and every sheet by group AAAA, BBBB, & CCCC and i want update data from master to every sheet. I got S.No. from master sheet but i am unable to get other data, Match is getting Error
I use this formula to get S.No From Master Data
I got S. No like this
=IFERROR(IF(OFFSET('Master Data'!\$A\$3,1+C2,0)>0,OFFSET('Master Data'!\$A\$3,1+C2,0),""),"")
=IFERROR(IF(OFFSET('Master Data'!\$A\$65,1+C1,0)>0,OFFSET('Master Data'!\$A\$65,1+C1,0),""),"")

\$A\$3= AAAA
\$A\$65=BBBB
But i can't get another Date like column B Has ID NO. which i cant use index match for all over worksheet

is there someway to do it?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### djo5003

##### New Member
What is in C1 & C2?

##### MrExcel MVP
Post a sample along with the desired results (with reference to any formula).

#### kumar lama

##### Board Regular
first formula is for one sheet and 2nd formula for another sheet, C1 Or C2 is just blank cell, so when i drag Horizontal i can have +1 S No, and when last number reach in text for eg \$A\$65=BBBB i will get Error, to remove error i use iferror
let me explain more about my question the S. No i am getting Ok from master data S.no is in A column in master data and i want get data from B column also, i want like below
=index('Master Data'!\$B\$3:\$B\$2000,match(C3,'Master Data'!\$A\$3:\$A\$2000,0))
i know why this formula is not working because in master date same S. NO Repeat 2 or 3 times like i explain in first question. i can do it from Vlookup and select only Group referance but i want select all in one time.

##### MrExcel MVP

Can't you post a small sample along with the desired results, without any formula please?

#### kumar lama

##### Board Regular
Sample File
Master Sheet
 Master Data S.No ID NO Name AAAA 1 D1 AA 2 D2 BB 3 D3 CC BBBB 1 S1 DD 2 S2 EE 3 S3 FF 4 S4 GG

<tbody>
</tbody>

Sheet AAAA =IFERROR(OFFSET(\$A\$3,1+G9,0),"")
 Sheet AAAA S.No ID NO Name 1 INDEX(\$B\$4:\$B\$11,MATCH(G10,\$A\$4:\$A\$11,0)) 2 D2 3 D3

<tbody>
</tbody>

Sheet BBB
 Sheet BBBB S.No ID NO Name 1 D1 2 D2 3 INDEX(\$B\$4:\$B\$11,MATCH(J12,\$A\$4:\$A\$11,0)) 4 INDEX(\$B\$4:\$B\$11,MATCH(J13,\$A\$4:\$A\$11,0))

<tbody>
</tbody>

##### MrExcel MVP

Sample File
Master Sheet
 Master Data S.No ID NO Name AAAA 1 D1 AA 2 D2 BB 3 D3 CC BBBB 1 S1 DD 2 S2 EE 3 S3 FF 4 S4 GG

<TBODY>
</TBODY>

...

Are S.No values always sequential, i.e., 1, 2, 3, etc. per sheet name?

#### kumar lama

##### Board Regular
yes it always by sequential

##### MrExcel MVP
yes it always by sequential

A1:C10 of master houses the data...

 S.No ID NO Name AAAA 1 D1 AA 2 D2 BB 3 D3 CC BBBB 1 S1 DD 2 S2 EE 3 S3 FF 4 S4 GG

<TBODY>
</TBODY>

Go to AAAA

 AAAA 1 4 S.No ID NO Name 1 D1 AA 2 D2 BB 3 D3 CC

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

A1, just enter:
Rich (BB code):
``````=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")
``````

A2, just enter:
Rich (BB code):
``````=MATCH(\$A\$1,master!\$A\$2:\$A\$10,0)
``````

A3, control+shift+enter, not just enter:
Rich (BB code):
``````=LOOKUP(9.99999999999999E+307,
CHOOSE({1,2},MATCH(9.99999999999999E+307,master!\$A2:\$A10),
LOOKUP(9.99999999999999E+307,IF(ROW(master!\$A\$2:\$A\$10)-ROW(master!\$A\$2)+1 >
MATCH(\$A\$1,master!\$A\$2:\$A\$10,0),IF(ISTEXT(master!\$A\$2:\$A\$10),
ROW(master!\$A\$2:\$A\$10)-ROW(master!\$A\$2))))))
``````

A5, just enter and copy down:
Rich (BB code):
``````=IF(ROWS(A\$5:A5)<=\$A\$3-\$A\$2,ROWS(\$A\$5:A5),"")
``````

B5, just enter, copy across, and down:
Rich (BB code):
``````=IF(\$A5="","",OFFSET(INDEX(master!A\$2:A\$10,\$A\$2),\$A5,1))
``````

Implement exactly the same set of formulas in BBBB.

See the workbook:
https://dl.dropboxusercontent.com/u/65698317/kumar lama distribute to sheets.xlsx
for the implementation.

#### kumar lama

##### Board Regular
Mr. Aladin Akyurek Thank You for your help and hard work. In here sheet AAAA is working fine but sheet BBBB is not working, because in sheet AAAA formula "=MATCH(\$A\$1,master!\$A\$2:\$A\$10,0)" \$A\$10 is changing as i insert New Row, But sheet BBBB i dont have to insert row, just i will add S. NO so the formula is not worked

and the array formula is really Long, very hard to ulderstand :P, i cant figure out what Choose() & Istext is doing there?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,148
Messages
5,835,680
Members
430,375
Latest member
datdog22

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back