Locating and returning cell value

[Tessag]

Good evening

This is my first time posting on this forum so I apologize in advance if I step out of line in any way, shape or form.

Please can I ask for assistance with a formula that will help me to locate and return a value.

I have a 120 line (not too big) list of values. These values range between 0 and 400.

I want to extract the relevant values within specified parameters (e.g. 0 - 99, 100- 199. 200 - 299, 300 - 399) and display in 4 separate columns.

Thank you for any assistance in this regard.

T

 

[Momentman]

The Data is in cells A1 to A120 for example, I just showed an extract of the result

Book1
	E	F	G	H
3	0-99	100-199	200-299	300-399
4	63	147	224	344
5	31	128	243	313
6	76	194	281	324
7	12	159	285	333
8	30	105	258	340
9	78	166	256	389
10	45	161	228	354
11	13	163	245	368
Sheet1

Cell Formulas
Range	Formula
E4:E37,H4:H28,G4:G34,F4:F33	E4	=FILTER($A$1:$A$120,($A$1:$A$120>=LEFT(E$3,FIND("-",E$3)-1)+0)*($A$1:$A$120<=RIGHT(E$3,LEN(E$3)-FIND("-",E$3))+0))
Dynamic array formulas.

 

[Tessag]

Thank you Momentman for your quick assistance.

Please forgive my limited understanding in this regard.

I have inserted the formula as provided but it keeps given me an error on the formula.

I'm also struggling to understand the relation between the original data range in Col A and the formula insertion in Col E to H, and the application of the Range as you stipulated.

Again, sorry for all the uncertainty. I'm still learning

Regards

T

 

[Momentman]

@Tessag : Not a problem at all, the filter function works in Office 365 and as i can see you are using that

Essentially when i have 0-99, i am filtering the data set to show me data that is greater than equal to 0 and less than equal to 99 and the same for other columns

See if the sample workbook below helps

Sample Workbook

 

[Tessag]

@Momentman : Thank you so very much.

You are a lifesaver!

I have learnt something new

Thank you for your time. I really appreciate it.

T

 

[Momentman]

Y

@Momentman : Thank you so very much.

You are a lifesaver!

I have learnt something new

Thank you for your time. I really appreciate it.

T

You are welcome. A pleasure to have been able to help