Offset index match

[kumar lama]

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?

thank you in advance,

 

[djo5003]

What is in C1 & C2?

 

[Aladin Akyurek]

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

 

[kumar lama]

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.

 

[Aladin Akyurek]

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

 

[kumar lama]

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>

 

[Aladin Akyurek]

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]

yes it always by sequential

 

[Aladin Akyurek]

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:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

A2, just enter:

=MATCH($A$1,master!$A$2:$A$10,0)

A3, control+shift+enter, not just enter:

=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:

=IF(ROWS(A$5:A5)<=$A$3-$A$2,ROWS($A$5:A5),"")

B5, just enter, copy across, and down:

=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]

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?