Excel - COLUMN Function Problem - Expert Solution

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
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
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
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
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
Excelchat Expert 12/09/2018 - 05:06
Have one table in - hang in there
User 12/09/2018 - 05:07
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
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
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

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc