Using Date Range For VLookup?
Feb 13, 2013
I want to run a vlookup (against cell D1 for this example), from a selection of date cells in column A (cell A1 in this example), where the date can be plus or minus a set number of days from the value in the cell (2 days either way in this example).
When I try the following it does not work:
if((A1-2)>A1>(A1+2)=D1,vlookup(D1,D:E,2,0))
Just to clarify, if D1=05/11/2012, I want the vlookup to return if cell A1 is equal to Nov 3rd, 4th, 5th, 6th, or 7th.
PS: =if(or(a1-2=d1,a1-1=d1,a1=d1,a1+1=d1,a1+2=d1),vlookup etc etc) returns the correct result, but if the date range I search on changes I don't want to rewrite the formula in this format.
View 7 Replies
ADVERTISEMENT
Apr 10, 2014
It's a quick formula question that I have trouble solving..
See attached : LookupSample.xlsx
View 3 Replies
View Related
Feb 2, 2009
I am hoping to do, is change a table's information based on a date range that is specified by the user. I have attached an example workbook below. In the example, on the Data sheet, there is the raw data information, showing user name, the date in question, and finally a number that refers to employee productivity on said date.
What I am hoping to do is to incorporate a vlookup function in order to change the data recovered on the Chart sheet by having the user select a date range. So that if you wanted to view the statistics for just Dec 15, 2008, it would only poll the data from that date, and if you wanted to view the statistics for any dates between Jan 1, 2009 and say Jan 30, 2009, it would do the same.
View 2 Replies
View Related
Feb 18, 2010
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ).
The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me
The problems I need solving are:
I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this?
I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
View 9 Replies
View Related
Feb 13, 2014
I am attempting to find a way to search through a database I created based upon every day of the year. My goal is to have a user input a beginning date and end date for a trip, then my functions will return the range specified with each day and it's individual temperature average (in my database).
I also need generating the dates between the start and end dates that are entered by the user. I was considering combining Days360 with an Hlookup that searches through a massive sheet with all the dates for the upcoming year and then going down 1 in the column for the difference in the dates in each column.
Vlookup Help.xlsx
View 5 Replies
View Related
Feb 13, 2014
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))
View 5 Replies
View Related
Jan 5, 2012
I have a table with three columns. I'm building a calendar on a separate worksheet and am looking up the "value" based on a calendar date. So if a date falls within any of the ranges, I'd like to return the value in column C. For example, if the date is 02/07/12, I'd like for the result to be value 1, or if the date is 04/17/12, then I would like the result to be value 3. I've used a nested vlookup, but all that give me is the value when either the start or end dates match, but I can't get a value when the date falls within the range. If the dates were consecutive, I would simply use vlookup/TRUE, but the dates are not consecutive.
ABC102/06/1202/09/12value 1203/12/1203/15/12value 2304/16/1204/19/12value 3405/21/1205/24/12value 4506/25/1206/28/12value 5606/25/1206/28/12value 6
View 4 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
May 1, 2014
I have a employee travel spreadsheet with air travel dates in two columns. I want to be able to create a rule that will highlight all the cells in a row if the date range on that row fall includes today's date. if someone is traveling in the event there is an emergency and I need to know if they are traveling that particular day. I have attached a sample spreadsheet.
View 3 Replies
View Related
Oct 11, 2008
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
View 2 Replies
View Related
Mar 28, 2009
I have two sheets on a workbook.
SheetA is the mastersheet containing the reference numbers
SheetB contains some of the references numbers and a date next to each reference number.
This is my formula: =VLOOKUP(A2,'23March'!A:K,10,FALSE) to get the date reflected in the Master sheet and this is what I get: 0/01/00
Both columns are date format. The reference number column is formatted as number ( I tried general format too and it made no difference)
View 9 Replies
View Related
Feb 22, 2010
I have a list that I want to use in a VLookup. Column A has a type and Column B has a last used date. The last used date will not be arranged chronologically. I want a VLookup to show me the most recent date that a type was used. VLookup might not be the best solution, but I don't know what is!
Example below:
Pizza
1/5/2010 Pizza
1/10/2010 Burger
2/15/2010 Salad
1/15/2010 Burger
1/15/2010 Soup
1/10/2010 Soup
2/15/2010 Pizza
1/28/2010 Salad
1/29/2010 Burger
1/30/2010 Burger
1/30/2010 Salad
1/15/2010 Burger
1/28/2010
Most recent date Pizza
Burger
Salad
Soup
View 9 Replies
View Related
Dec 29, 2007
I am trying to use worksheetfunction.vlookup. with a date as the lookup item and key field. funcion works when I test using integers, but fails when I use dates.
Sub vlok()
c = DateSerial(2007, 12, 2)
c = Application.WorksheetFunction.VLookup("c", Range("tab1"), 2, False)
End Sub
View 2 Replies
View Related
Feb 15, 2009
I'm having trouble getting my vlookup formula to work in vba. Basically, I have a range of dates in column A and want to the vlookup to fill these in until there are no dates available. The reason I want to do this in VBA opposed to on the spreadsheet, is that I have large subset of time series data which needs to be frequently updated and wish for the data in column C to be updated everytime the macro is run.
My code is below.
View 12 Replies
View Related
Dec 30, 2009
I am setting up a VLookup system in which hundreds of excel worksheets are refering back to one master price list. How can I keep everything up to date if the master list is changed? My ideas: Is there a way to sync multiple worksheets (at the end of the workday, maybe)? Is there a way to keep the master sheet open in the backround so that each new worksheet I open gets the updated information? Or perhaps there is an easier way?
I don't know code or anything fancy like that so a simple solution (even if it's not the best method) will do for now. *Another note: these worksheets will be saved on a network available to up to 3 computers at a time.
View 4 Replies
View Related
Feb 26, 2009
I'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.
Atm, it reads some like this
View 3 Replies
View Related
Jan 2, 2014
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
View 1 Replies
View Related
Mar 5, 2014
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
View 1 Replies
View Related
May 26, 2014
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies
View Related
Apr 21, 2006
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
View 4 Replies
View Related
Jul 1, 2008
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
View 2 Replies
View Related
Mar 28, 2014
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
View 13 Replies
View Related
Jun 20, 2013
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
View 12 Replies
View Related
Jan 22, 2009
My original formula is:
=IF(ISBLANK(VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,FALSE)) = FALSE,VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,$E39),"")
I am using the above formula but would like it to get the value when the date has passed (when date has been inputed in another cell)
I have added an attachment, The formula begins at J44 to AY44 but when date has entered in the red (D44:D67)section I would like to retreive values for blue section(Q44 and onwards) and not the yellow section(J44 to P44)
This is so when players make a transfer it doesnt include the weeks before that date!!
View 12 Replies
View Related
Sep 22, 2009
i have attached an example to illustrate the data source and result expected.
View 3 Replies
View Related
May 23, 2008
I am having trouble doing a VLOOKUP, whereby I have a column of staff that are frequently detached from the workplace (See extract sample). One member of staff could be away 4 times in a year and I need to place the most relevant detached date (which would be 2 months ago and 4 months from today) into another table. This other table would then show all members of staff in alphabetical order with just one detached and return date. If the date falls out of the above parameters (6 month window), nothing would be displayed.
View 13 Replies
View Related
Oct 31, 2011
I have 1 file with 2 worksheets, MR and MR (ytd). I want to write 1 procedure for a report to lookup the container no at MR from MR (ytd), then return the date.
Now I just use the formula vlookup to do this function.
=IF(ISERROR(VLOOKUP($B5,'MR (ytd)'!$B$5:$O$805,14,0)),"0",VLOOKUP($B5,'MR (ytd)'!$B$5:$O$805,14,0)+$B$2-'MR (ytd)'!$B$2)
I have write up some code for it. But find it is cannot lookup.
Code:
Sub mrdays()
Dim mrrecords As String
Dim i As Integer
Dim to_date As Date
Dim lsrpdate As Date
With Sheets("MR")
[Code] .........
View 3 Replies
View Related
Jul 8, 2006
I have a table, which is a price list, with different prices based on different dates. For instance, the dates may be 1/1/04, 6/1/04, 7/1/04, 10/30/04, etc. I have some dates in another table, for instance, 2/2/04, 7/5/04, 12/20/04, etc. I want to lookup the date closest to, but not greater than, the last previous date posted. So, for the first date of 2/2/04, you would use the column with date 1/1/04 from the first paragraph. For a date of 7/5/04, you would use the column with date of 7/1/04. I think this is a simple VLOOKUP function, but not sure how to do a date compare. Also, the lookup has to do two things. First, it has to find the part number in the table, and then match it do the respective most recent date.
Table setup:
Part Number ---- Date
5555555555 ---- 7/5/04
Lookup table:
Part Number ---- 1/1/04 ---- 6/1/04 ---- 7/1/04
5555555555 ---- $65.32 ---- $68.52 ---- $71.52
I want to grab the price with the respective date from the first table. In this case, it would grab the price of $71.52 from the lookup table.
View 7 Replies
View Related
Jun 6, 2008
I haven't had issues with vlookup on other worksheets, but this one is giving me trouble. I have attached the excel file for your consideration.
The lookup value is in cell U2, formatted as a date
The table array is V2:Z19
The column index number is 2 for C3
False lookup
I've tried the formula with and without the text function for the date and I've also tried different formats for the dates in columns V to Z because I know the format of the cells have to match for vlookup. It is very frustrating because this should be simple, but it is messing up.
View 2 Replies
View Related
Dec 12, 2011
way to search and display a date from a range of cells based on less than or greater than criteria. For example I have following dates in column A:
A1: 2011/01/04
A2: 2011/02/01
A3: 2011/03/01
A4: 2011/04/01
I want to search for the date which is less than 2011/02/01 from the A column and display that date in B1 cell for example. How do I do that?
I want to do this without using any macros.
View 9 Replies
View Related
