Offset index match

kumar lama

Board Regular
Joined
May 20, 2014
Messages
84
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,
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

kumar lama

Board Regular
Joined
May 20, 2014
Messages
84
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

kumar lama

Board Regular
Joined
May 20, 2014
Messages
84
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.NoID NOName
1D1AA
2D2BB
3D3CC

<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
Joined
May 20, 2014
Messages
84
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,081
Latest member
Subaru_Steve

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top