Using the FIND and OFFSET functions, combined with the MATCH function in the one formula

[gsdanger]

Hi Guru’s

I need your assistance again…

I have a list of data, segregated into data ranges (1, 2, 3 etc…).

I select each data range by using the Match function (works well).

Within each data range, I want to find a specific word ("Overall"...common in all the data ranges) and then use this word as the starting point for using the OFFSET function to copy the next cell down into a blank cell.

I would like to have the functions of FIND and OFFSET combined into the existing MATCH function.
However, if this is not possible, then a combination of the FIND and OFFSET functions will be great

Any assistance would by much appreciated.

Kind Regards…gsdanger

 

[Peter_SSs]

Could you give us a small set of dummy sample data and the expected results with XL2BB so we have a better idea of what you have and what you are trying to achieve?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[gsdanger]

Hi Peter_SSs,
I have been having trouble with the XL2BB for years and still having trouble.
I managed to progress through to the XL2BB option and managed to follow the instructions for creating a mini sheet. The program indicated that the mini sheet was successfully copied to my clipboard....great!
However, I cannot upload the image as it tells me that the mini sheet is in the wrong format!
Stuck again!!!
Any suggestions please??

 

[Peter_SSs]

However, I cannot upload the image

There is no image to upload, just paste the clipboard into your post and then use the 'Preview' option to see what the mini sheet will look like.
When you first paste the clipboard into your post it would look something like below (though probably a fair bit bigger)
When you then Preview or Post your reply it will transform into a Mini Sheet.

[RANGE=rs:3|cs:1|w:24 12 13.xlsm|cls:xl2bb-210|s:Sheet3|tw:48][XR][XH][/XH][XH=w:48]A[/XH][/XR][XR][XH]1[/XH][XD=ch:12.75]1[/XD][/XR][XR][XH]2[/XH][XD=ch:12.75]2[/XD][/XR][XR][XH]3[/XH][XD=ch:12.75]3[/XD][/XR][/RANGE]

 

[gsdanger]

There is no image to upload, just paste the clipboard into your post and then use the 'Preview' option to see what the mini sheet will look like.
When you first paste the clipboard into your post it would look something like below (though probably a fair bit bigger)
When you then Preview or Post your reply it will transform into a Mini Sheet.

[RANGE=rs:3|cs:1|w:24 12 13.xlsm|cls:xl2bb-210|s:Sheet3|tw:48][XR][XH][/XH][XH=w:48]A[/XH][/XR][XR][XH]1[/XH][XD=ch:12.75]1[/XD][/XR][XR][XH]2[/XH][XD=ch:12.75]2[/XD][/XR][XR][XH]3[/XH][XD=ch:12.75]3[/XD][/XR][/RANGE]

 

[gsdanger]

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Result of Raw OFFSET			Data Block Validation	The Confirmed Data Block	The Data Range
2
3
4		No1 Result	12-34-56			TRUE	1	1
5		No2 Result	34-56-78			FALSE		Tom
6		No3 Result	45-67-89			FALSE		QUICK
7						FALSE		BROWN
8						FALSE		FOX
9	No1. Combined Formula Result					FALSE		Overall
10	No2. Combined Formula Result					FALSE		12-34-56
11	No3. Combined Formula Result					TRUE	2	2
12						FALSE		****
13						FALSE		OVER
14						FALSE		THE
15						FALSE		LAZY
16						FALSE		DOG
17						FALSE		Overall
18						FALSE		34-56-78
19						TRUE	3	3
20						FALSE		Harry
21						FALSE		OVER
22						FALSE		THE
23						FALSE		LAZY
24						FALSE		DOG
25						FALSE		Overall
26						FALSE		45-67-89
27						FALSE
28
Sheet1

Cell Formulas
Range	Formula
C4	C4	=OFFSET(H9,1,0)
C5	C5	=OFFSET(H17,1,0)
C6	C6	=OFFSET(H25,1,0)
G4:G19	G4	=IF(F4=TRUE,H4,"")
F4:F27	F4	=IF(H4=1,TRUE,IF(H4=2,TRUE,IF(H4=3,TRUE)))

Hi Peter_SSs,
Hooray, I just managed to upload the sample spreadsheet, as requested.
What I am trying to achieve is to get the data immediately below the starting point word (Overall) to be placed in an empty cell so I can use this info for further analysis. Naturally, this spreadsheet is just a dummy example, as the data I will be using is very large.
The problem arose when the downloaded data I use from the web kept altering the number of rows in the page. I have been using the cell addresses in conjunction with the MATCH function to glean the data to another spreadsheet, for further analysis. However, when the row number changes, it makes my formulas irrelevant.
I thought that if I selected a constant word that is in all of the data ranges (the data ranges are always of differing row sizes) and then use the FIND and OFFSET functions for collecting this data, I would not have to rely on the actual cell references.
If you think there is another way I can do this (probably is??) please educate me.
Hopefully, you can assist me with this problem.

Kind Regards.....gsdanger

 

[Peter_SSs]

Thanks for the XL2BB data.
Is this what you are trying to do?

24 12 13.xlsm
	B	C					H
4	No1 Result	12-34-56					1
5	No2 Result	34-56-78					Tom
6	No3 Result	45-67-89					QUICK
7							BROWN
8							FOX
9							Overall
10							12-34-56
11							2
12							****
13							OVER
14							THE
15							LAZY
16							DOG
17							Overall
18							34-56-78
19							3
20							Harry
21							OVER
22							THE
23							LAZY
24							DOG
25							Overall
26							45-67-89
27
28
29
30
31
Overall

Cell Formulas
Range	Formula
C4:C6	C4	=FILTER(H5:H30,H4:H29="Overall")
Dynamic array formulas.

 

[gsdanger]

Hi Peter_SSs,
OMG!!
How on earth did you come up with this solution...How good have you done!!!
I would not have even thought of this as a solution.
Thank you for saving me countless hours (or weeks) of hair pulling.
Thank you very much Peter_SSs.
I owe you a beer or two!!!...Once again, Thank you...

Kind Regards...gsdanger

 

[Peter_SSs]

You're welcome. Glad it helped. Thanks for the follow-up.