Using VLookup To Lookup Date Within Multiple Date Ranges
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
ADVERTISEMENT
Sep 23, 2013
I have a spreadsheet that records the date, specific sporting activities and cash award for participation in a round robin sporting event that was held over 52 weekends. I'm trying to lookup the cash award value of each event the person participated in by using a date range within the vlookup formula.
Each participant has their own spreadsheet which they are able to access via server, and each spreadsheet looks a bit like this:
-Column A contains the date of an event in which the person participated
-Column B contains a category number related to the type of event eg marathon = 12, hurdles = 15, relay = 18 etc. This info is manual input
-Column C contains the amount the person will be awarded by simply participating in the event. This is a lookup value and varies depending on the date the person participated, so I'm trying to incorporate date ranges and 'if' formulas into the vlookup
I'm from Australia so my dates might be backwards...
Spreadsheet:
A B C
1 02/01/13 15 (supposed to be $10)
The formula should read a bit like this:
if (date at A1 is within date range 1st Jan 2013 and 31st March 2013, lookup B1 in table 1 column 2), if (date at A1 is within 1st Apr 2013 and 30th Jun 2013, lookup B1 in table 2 column 2)...etc for each quarter
I don't know if the following is right
=IF(AND(A1>='01/01/2013'+0,A1='04/01/2013'+0,A1
View 5 Replies
View Related
Nov 8, 2012
I have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,
I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.
I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.
=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")
It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.
The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.
Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!
I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.
I hope this becomes a bit clearer on the sheet I have attached,
View 9 Replies
View Related
May 1, 2009
attached worksheet.
The worksheet has two sections, Extra payments and Loan Schedule.
If a date in the Extra Payments section is within a range in Loan Schedule, the enter its corresponding value, or sum of values in the Loan schedule under its respective date.
the attached sheet explains in detail.
View 7 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 15, 2012
I would like to utilize the IF function to show the following:
If the date in cell J11 is before April 30, J19 will show J18 * .01, if the date in cell J11 is between May 1 and May 30, J10 will show J18 * .02, and if the date in cell J11 is between June 1 and June 30, J10 will show J18 * .03.
View 3 Replies
View Related
Jun 18, 2014
My problem is trying to identify the applicable rate in a range of dates that are not consistent in every case. I have a number of orders that span 4 years. The rate charged has changed over time and therefore I'm trying to find what the applicable rate would be for that time frame. For example one of my orders was created on 2/27/2012 with a specific item, then again the same item was ordered on 9/10/2013 and I need to find what the rate should have been for both of those orders during those rate periods. I've attached a sample sheet with the 2 tables I've been trying to assess. I've tried using mulitple IF and VLOOKUP formula's but it doesn't work they way I need it to due to the inconsistency in Table 2. H
View 2 Replies
View Related
Oct 23, 2009
My setup is - excel 2003 sp3 / windows xp
On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths.
Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received.
=SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27
View 9 Replies
View Related
Sep 20, 2013
I am currently trying to make a digital time card for my place of employment. I has an odd set up and odd date ranges. The pay period starts on the 21st of each month and ends on the 20th of the next month.
The time cards have 5 columns for each week starting on Mondays going to Sunday (also weird its not Saturday to Sunday) What I was hoping to accomplish was being able to select the starting month of your choice for pay period and have multiple cells update the week date range.
Date
Date
Date
Date
Date
**Here would be the drop down month select.
Sept 21 - Sept 22
Sept 23 - Sept 29
Sept 30 - Oct 6
Oct 7 - Oct 13
Oct 14 -Oct 20
View 2 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
Dec 30, 2008
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.
View 9 Replies
View Related
Oct 12, 2012
I'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.
I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.
View 3 Replies
View Related
Dec 30, 2008
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.
View 7 Replies
View Related
Aug 11, 2013
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
View 1 Replies
View Related
May 8, 2014
Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"
View 3 Replies
View Related
Feb 17, 2009
i want a macro to search all of column A in multiple sheets for a date that has been put into an input box.
the matching dates have data corresponding to them in that row. i want the date and data from the row to then be copied into a new sheet.
View 4 Replies
View Related
Mar 13, 2014
I have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........
View 9 Replies
View Related
Mar 1, 2007
I want to have a new macro/button to redo the chart cells based on the dates in cols T and U (abt 400 entries). T has the start date and U has the end date.
Row 9 - X9: CJ9 have dates for about 10 months (weekdays only)
Column J have either of these two values: A or B
Column C has either of these two values: C or blank
I would envisage it going through each row, checking:
If date at top of that col was between the dates in cols T & U
then
if col J = 'A' then set cell to black block
else if col J = 'B'
then
if col C = C (closed) then set cell to red block
else set cell to blue block
View 2 Replies
View Related
Apr 21, 2006
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View 7 Replies
View Related
Jan 23, 2012
I have a list of venues and a start date, end date and number of items per day to be installed, which looks like
A - B - C - D
Venue 1 - 01/01/12 - 31/01/12 - 15
Venue 1 - 03/01/12 - 30/04/12 - 10
Venue 2 - 05/01/12 - 28/02/12 - 9
And I want to summarise this in a table with one row for each day of the year which looks like
A - B - C
1/1/12 - Venue 1 - 15
2/1/12 - Venue 1 - 15
3/1/12 - Venue 1 - 25
4/1/12 - Venue 1 - 25
5/1/12 - Venue 1 - 25
etc etc
I can't work out the formula to put in Column C on my second table which will lookup the venue name and then put in the sum of the daily values.
View 7 Replies
View Related
Jun 15, 2008
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies
View Related
Feb 21, 2013
Im trying to make a vlookup so I can say find a record where column A is >4, B is =2, C is 1.
Example - The lookup would find these 2 ranges.
7
2
3
2
6
4
[code]....
View 1 Replies
View Related
Jun 25, 2014
I am looking for the formulae in E9.
I may change the value in E8 as 1.5 or 2.5 or 4 or 6 I may scrolldown C9 as it set out there. I need to know the farmulae in E9.
View 6 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
Jan 20, 2010
I've information in two sections of a worksheet, columns A and B (range named "temp1") and columns D and E (range named "temp2"). I should also say that the reason I have two sections is that each section will have about 150 rows, so instead of having a huge list of 300 rows, I've tried splitting them into two sections.
AB
1a
1b
3c
DE
4d
1e
6f
What I want to do it show all the results that match the value 1 using a vlookup formula. Also the values in column A and D may change, i.e if value 4 in D1 changed to 1, I would expect to see "d" included in the results.
This is the original formula I got somewhere else which sorts out my vlookup on multiple matches issue.
View 4 Replies
View Related
Feb 23, 2012
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Sheet8.Range("C16:Y1000")) Is Nothing Then Exit Sub
Dim rw As Integer
Dim arw As Integer
rw = 16
[Code] .....
As you can see, my code is located in the sheet8 worksheet object. Now, I have a few questions about this. Because I am located in the sheet8 worksheet object does that mean my code can only work in sheet8, i.e., the following won't work because I am in a Sheet8 worksheet object?
Sheet10.Range("B12) = ..... ....... .....
This is not returning a value in Sheet10? My question is how do I make my code return a value in Sheet10?
View 6 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
Feb 14, 2014
accomplish VLOOKUP for multiple instances of a same Lookup value
First let me explain about my file:-
Table~1:-
1.B2:B19 I have list of items which required to build a product
2.D2:D19 I have list of shortages against each item
3.C2:C19 I have list of dates when each item shortage will get fulfilled
Table~2:-
1.C22:C31 I have picked the largest 10 different dates from Table1 by using LARGE formula.
Against each date which is update C22:C31, respective shortage qty has to get VLOOKUPED.
When there is a same lookup value, it has to keep pick next value (Shortage Qty) for same date.
View 2 Replies
View Related
Dec 3, 2009
I'm trying to get a result of all rows in a table that has the same lookup value.
For example:
David25
Brian40
Steve57
David68
David902
John11
Gerry3
I want to look for David and that the result will be all rows that starts with David:
David 25
David 68
David 902
I tried using vlookup but it always returns the first row.
View 3 Replies
View Related
Jan 8, 2009
ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10
Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.
Is there someway to do this with a vlookup or maybe match index?
View 9 Replies
View Related
