**Question description:**

*This user has given permission to use the problem statement for this blog.*

indexing multiple rows of data from criteria in a column (need to be able to bring up multiple job specifics [each job has its own row] by searching in the "due date" column for jobs due between start date and end date

Solved by O. S. in 55 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
12/09/2018 - 04:38

Hi mate

Excelchat Expert
12/09/2018 - 04:38

Hi Welcome!

User
12/09/2018 - 04:38

I am hoping you can help me out here- I've found a youtube video but it seems that it might get complicated with the kind of data i use

Excelchat Expert
12/09/2018 - 04:39

Ok, do you have a sheet to upload?

User
12/09/2018 - 04:39

basically i need to extract all rows from a range hat meet criteria in one column

User
12/09/2018 - 04:39

I'll try and mock something up

User
12/09/2018 - 04:39

onemomentplease

User
12/09/2018 - 04:40

actually i should have done the mock up before connecting lol

User
12/09/2018 - 04:40

there's a lot of data

User
12/09/2018 - 04:40

I'm tr4ying to use a mix of indexing and array formulas

User
12/09/2018 - 04:41

i've got about 25 columnsn and 1000 rows of data

Excelchat Expert
12/09/2018 - 04:41

OK,

Excelchat Expert
12/09/2018 - 04:42

the structure and content is what we need to look at

User
12/09/2018 - 04:45

basically if I enter a start date and an end date- I need to pull all the row data for jobs that fall between those dates

Excelchat Expert
12/09/2018 - 04:46

Ok, need the dates in the same format to start

User
12/09/2018 - 04:47

day/month/year

Excelchat Expert
12/09/2018 - 04:47

Month, day , year is what I think you have , which is fine

User
12/09/2018 - 04:48

using this month and last month

User
12/09/2018 - 04:49

either way the formula should be the same format lol

Excelchat Expert
12/09/2018 - 04:49

To pull data , in basic EXCEL need another sheet or range with formula, or we create a table with filter

User
12/09/2018 - 04:50

i have 3 other sheets i'll be pulling the data from

User
12/09/2018 - 04:51

the date ranges are going to be entered in one sheet- and the results need to populate in a seperate sheet

User
12/09/2018 - 04:51

while drawing on the 3 source data sheets

User
12/09/2018 - 04:51

(all in seperate tabs inthe one file)

Excelchat Expert
12/09/2018 - 04:52

Let me take this to EXCEL and try a table solution,

User
12/09/2018 - 04:52

ok

Excelchat Expert
12/09/2018 - 04:52

Please stand by

User
12/09/2018 - 04:52

i can use indexing to draw the data from the sheets using:

User
12/09/2018 - 04:52

=INDEX('FBE Source Data'!$A$1:$V$1000,,'DO NOT TOUCH'!A2)

User
12/09/2018 - 04:53

but to get it to only show the data falling between dates is where i'mstuck

Excelchat Expert
12/09/2018 - 04:54

Not sure INDEX will do what you need here,\

Excelchat Expert
12/09/2018 - 04:54

dont you want the entire row?

User
12/09/2018 - 04:54

any help would be great

User
12/09/2018 - 04:54

yes- I just copy the row references across

User
12/09/2018 - 04:54

across eachcolumn

Excelchat Expert
12/09/2018 - 04:56

I will set up another sheet and pull data from 2, simple table setup

User
12/09/2018 - 04:57

thankyou

Excelchat Expert
12/09/2018 - 05:06

Have one table in - hang in there

User
12/09/2018 - 05:07

ok

Excelchat Expert
12/09/2018 - 05:13

I need more logic work to bring in a 2nd table, but I thought we should go over the concept now, in case time runs out

[Uploaded an Excel file]

Excelchat Expert
12/09/2018 - 05:14

The idea is just an IF AND formula and EXCEL table that filters out BLANKS.

Excelchat Expert
12/09/2018 - 05:15

To add another table or two need IF OR AND

Excelchat Expert
12/09/2018 - 05:15

and I need time to work that thru, but see if this makes sense first'

User
12/09/2018 - 05:15

ok thankyou- I'll look into it and see how I go

Excelchat Expert
12/09/2018 - 05:17

I can send a final via site support , want to build out and test the IF OR AND,,fir 3 tables

Excelchat Expert
12/09/2018 - 05:17

Is that OK?

User
12/09/2018 - 05:18

and to add the other sheets - iu just use the same formula at the end of the first prefixed with a comma?

Excelchat Expert
12/09/2018 - 05:18

Yes,

User
12/09/2018 - 05:19

that should work- if it displays in the same results table

User
12/09/2018 - 05:19

thankyou very much

Excelchat Expert
12/09/2018 - 05:19

and you dont need the complex OR AND, you canust

Excelchat Expert
12/09/2018 - 05:19

sorry , just cover the range of all 3 tables with the current formula

Excelchat Expert
12/09/2018 - 05:20

the TABLE setup with BLANKS suppressed will bring all the data together

User
12/09/2018 - 05:22

so when you mentiuion cover all three ranges with the same formula- do you mean:

User
12/09/2018 - 05:22

=IF(AND(Sheet1!$D2>=Summary!$H$2,Sheet1!E2<=Summary!$J$2),Sheet1!B2,""), IF(AND(Sheet2!$D2>=Summary!$H$2,Sheet2!E2<=Summary!$J$2),Sheet2!B2,"")

User
12/09/2018 - 05:22

or would i remove the second argument "IF"

Excelchat Expert
12/09/2018 - 05:23

Use the same formula -one IF

Excelchat Expert
12/09/2018 - 05:24

Just make sure to change the SHEET reference to bring in the other sheet data

Excelchat Expert
12/09/2018 - 05:24

this is simple method

Excelchat Expert
12/09/2018 - 05:24

If all sheets are the same, you can combine then just use one formula

User
12/09/2018 - 05:25

can i be a bother and ask you to type it out as it didn't seem to work onmyend

Excelchat Expert
12/09/2018 - 05:25

Let me get you a 2nd example with 2 sheets

User
12/09/2018 - 05:25

its returned #VALUE error

User
12/09/2018 - 05:26

ok yes please

Excelchat Expert
12/09/2018 - 05:29

[Uploaded an Excel file]

Excelchat Expert
12/09/2018 - 05:29

This is covers both tables of only 9 rows, you have 1000 each , but concept is the same

Excelchat Expert
12/09/2018 - 05:30

Copy 1000 formulas, then switch sheet reference, then re-peat

User
12/09/2018 - 05:31

ok I'll try that out- thanks for the helpd

User
12/09/2018 - 05:32

ok that seems to work- much obliged- youve beenb a great help

Excelchat Expert
12/09/2018 - 05:33

OK,, this will work, just need to get all 1000 rows covered from each sheet and use an EXCEL table.

User
12/09/2018 - 05:33

yeah

Excelchat Expert
12/09/2018 - 05:33

Please stop back if you have any problems

User
12/09/2018 - 05:33

will dpo

Excelchat Expert
12/09/2018 - 05:33

Thanks and bye!

User
12/09/2018 - 05:33

cy

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*