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

L

#### Legacy 327817

##### Guest
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!

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You could try this:

Excel 2012
ABCDEF
1PersonProduct1Product2Product3Product480000-89999?
2Person 18700474402Yup
3Person 255565600305999334994Nope
4Person 374449600305999383303Yup
5Person 460009Nope
6Person 5822346003059993Yup
7
8http://www.mrexcel.com/forum/excel-questions/858555-return-rows-records-certain-value-range.html
Sheet25
Cell Formulas
RangeFormula
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")

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
ABCDEF
1Person 1870047440287000
2Person 255565600305999334994
3Person 374449600305999383303
4Person 460009
5Person 5822346003059993
6
7
8Person 1
9Person 3
10Person 5
11
CRTL-SHIFT-ENTER.

Replies
0
Views
662
Replies
3
Views
775
Replies
3
Views
2K
Replies
15
Views
6K
Replies
1
Views
219

1,196,118
Messages
6,013,568
Members
441,773
Latest member
sarah7

### 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?

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