Return rows (or "records") with a certain value/range

[Legacy 327817]

Hello, I need some help please!

I have a long excel sheet of records and only want to see the records that have a 5-digit code in the 80000's.

For example,

Column A__________Column B__________Column C_________Column D_________Column E

Person 1___________87004_____________74402
Person 2___________55565_____________60030____________59993_____________34994
Person 3___________74449_____________60030____________59993_____________83303
Person 4___________60009
Person 5___________82234_____________60030____________59993

Not all of the records have the same quantity of columns (like example above). I only want to return the records that have at least 1 5-digit code, with a value of 80000 to 89999. The value (80000 to 89999) could be in 1 of any of the columns (we have about 20 columns - but there is 4 columns in this example). In the example above, this would somehow return Person 1, 3, and 5.

Thank you in advance!

 

[DRSteele]

You could try this:

Excel 2012
	A	B	C	D	E	F
1	Person	Product1	Product2	Product3	Product4	80000-89999?
2	Person 1	87004	74402			Yup
3	Person 2	55565	60030	59993	34994	Nope
4	Person 3	74449	60030	59993	83303	Yup
5	Person 4	60009				Nope
6	Person 5	82234	60030	59993		Yup
7
8	http://www.mrexcel.com/forum/excel-questions/858555-return-rows-records-certain-value-range.html
Sheet25

Cell Formulas
Range	Formula
F2	=IF(COUNTIFS(B2:E2,">=80000",B2:E2,"<90000")>0,"Yup","Nope")
F3	=IF(COUNTIFS(B3:E3,">=80000",B3:E3,"<90000")>0,"Yup","Nope")
F4	=IF(COUNTIFS(B4:E4,">=80000",B4:E4,"<90000")>0,"Yup","Nope")
F5	=IF(COUNTIFS(B5:E5,">=80000",B5:E5,"<90000")>0,"Yup","Nope")
F6	=IF(COUNTIFS(B6:E6,">=80000",B6:E6,"<90000")>0,"Yup","Nope")

 

[AhoyNC]

Try:
Change ranges to match your data.
If you are careful on how you lock the rows and columns you can just copy the formula down as needed.

This is an array formula and must be entered with

Excel Workbook
	A	B	C	D	E	F
1	Person 1	87004	74402		87000
2	Person 2	55565	60030	59993	34994
3	Person 3	74449	60030	59993	83303
4	Person 4	60009
5	Person 5	82234	60030	59993
6
7
8	Person 1
9	Person 3
10	Person 5
11
CRTL-SHIFT-ENTER.