SUM Using INDEX & MATCH (using Date Ranges)
Dec 28, 2006
B2 contains CITYNAME
A2 contains The name of the TAB the data relates too (although i cant find anyway of using this cell within a formula to read the relevant tab, maybe you know of a way)
This is the working code i currently have in cell B3, this is what i need to enhance.
=SUM(INDEX(ABCD!$1:$65536,0,MATCH(B2,ABCD!$1:$1,)))
This looks at the tab ABCD and sums all the values it finds in the column that matches the name supplied in cell B2 on Sheet1
Tab ABCD has dates within coulmn A, and Values in Column B, first row from Column B to Column Z contains the CITYNAME (coumn Z may become longer so this needs to be able to cope with that too.
What i would like to do is add into this an extra requirement, there must be a start and end date. I still need to use the match function as B2 changes using a drop down validation (containing CITYNAMES, these represent the columns within the tab ABCD
E1 Contains the STARTDATE
H1 Contains the ENDDATE
This is the closest I get, changing the start and end date does not give the expected result...
=SUM(--(ABCD!$1:$65536>=E1),--(ABCD!$1:$65536
View 9 Replies
ADVERTISEMENT
Sep 19, 2013
I have two columns both of which lengths will vary depending on the time-frame entered by the user but will be the same as each other for each run of the code. Because of that, I'm trying to create a named range for each column and then execute an Index Match with an Offset off of it. Below is my code which results in an "Object required" error message.
Code:
Dim Ticker As Long, Tick As Range, TIK As Range, RoleDt As Long, RD As Range, endDate As Variant
Ticker = Range("F" & Rows.Count).End(xlUp).Row
RoleDt = Range("E" & Rows.Count).End(xlUp).Row
Set TIK = Range("F2:F" & Ticker)
Set RD = Range("E2:E" & RoleDt)
cell.Formula = "=INDEX(RD,MATCH,(RC[-1],TIK,0)+1)"
View 9 Replies
View Related
Apr 3, 2014
I have two files
Say first file name is X and in work sheet 1 i have used name manager for two columns say "Series" and "Fund name"
Second file name is Y
R7C3 is Fundname of Y file
RC4 is Series Value of Y file
In the row of Y file , C 15 - I want to calculate the formula
Range("C15").FormulaArray = _
"=IF(ISNA(INDEX(Series,MATCH(R7C3&RC4,Fundname&Series,0))=RC4),""NO"",""YES"")"
I am not getting any error but the result is not correct , even for the series which is not there in my file "X" it says "YES"
See my codes below :
Dim colA As Variant
Dim colB As Variant
Dim WksDash As Workbook
Dim Wksfile As Workbook
Dim wksWatch As Worksheet
Dim WksDash2 As Worksheet
Dim Classunion As Variant
[Code] .....
View 2 Replies
View Related
Sep 8, 2009
Column A contains State names; column B contains a few cities for each of the states. Row 1 contains rank as the header row. Then the table begins in cell C2 with the rates. I have two of these (on separate sheets), one titled w dep (with dependants) and the other w-o dep (without dependants). On a third sheet I have 4 combo boxes.
1.Chooses between Dep or W-O Dep
2.Chooses Rank
3.Chooses State
4.Chooses City
Here are my questions.
•How can I filter down the cities so that if I chose Texas for example, only the cities from Texas would show up in my combo box?
•I have figured out the formula to display the results from the w dep sheet but cannot get the information from the w-o dep sheet. Here is the formula I’m using.......
View 3 Replies
View Related
Sep 26, 2011
I am trying to do index match function with criteria ranges in another worksheets. The code kept on giving me " #Value!"..
Here is the code:
Sub test1()
Dim myvalue As Variant
Dim wsname As String
Dim i As Integer
Dim j As Integer
Dim r1 As range
Dim r2 As range
Dim r3 As range
Dim r4 As range
wsname = "CustomerIDbyParts"
[Code] .......
The resulting cell of F5 kept on returning #Value!.. I suspect that my syntax for looking up the ranges from another worksheet is wrong?
View 3 Replies
View Related
May 23, 2013
is there any formula that i can use that will provide me with the most current termination date of a specific driver
i was thinking something along the lines of
=IFERROR(INDEX(I:I,MATCH(L2,MAX(H:H,0))),"-")
this obviously doesnt work, how to get where i need to be!?!
View 7 Replies
View Related
Jul 29, 2009
I have the following tables and would like to return the red cells via formula
MinContract psuedo
contractid WHERE Table1.'MinDateShip' between
Table2.'MinDateContract' AND Table2.'MaxDateContract'
AND Table1.ID = Table2.ID
AND Table1.ReportTypeDescription = Table2.ReportTypeDescription
Table1
ABCDEF1IDReportTypeDescriptionMinDateShipMaxDateShipMinContractMaxContract2TEST1OE02/28/0505/31/05n/a7773TEST1OE11/30/0501/15/068568564TEST2OE12/31/0412/31/04123123
Table2
ABCDE12IDReportTypeDescriptionMinDateContractMaxDateContractContractID13TEST1OE02/28/0302/28/0455514TEST1OD03/01/0505/31/0577715TEST1OE03/01/0505/31/0577716TEST1OD05/31/0505/31/0699917TEST1OE12/01/0507/01/0785618TEST2OD02/28/0302/28/0498719TEST2OE03/01/0505/31/0545320TEST2OE05/31/0505/31/0645521TEST2OD11/30/0405/31/0545622TEST2OE11/30/0405/31/05123
View 9 Replies
View Related
Aug 8, 2014
I have attached a sample workbook which shows my desired output.
In E3 I need a formula to index B3:B60 and bring back the value where D3 falls between the dates in A3:A60
I have manually typed 46 into E3 as 15/05/2013 is equal to or greater than 13/05/2013 but less than 20/05/2013
Attached File : index match between dates FF.xlsx‎
View 4 Replies
View Related
Jan 1, 2014
I would like to ask how can I get to work the index match function (if there's any formula other than this current function its fine) if i have a start date and end date as range date then another criteria for name to get my desired result. im having a hard time to explain so i attached a workbook so you can around it.
I found a formula and tweaked it but still cant get to work.
View 10 Replies
View Related
Jun 24, 2014
I have the first sheet named "DPF" where I have a column "Date", "FIT", "Group" On my second Sheet named "Tracker" what I do want to do:
I select a date for example 18-07-2014 (Cell E3), if cell E4 I Have "Fit" and F4 "Group" In C6 I have E3-15, to get the date minus 15 days (03-07-2014) In C7 I have E3-12 (06-07-2014) and so on
What I want is, if the date of today is C6 or C7 or whatever, use the index match function to get the number corresponding to the date chosen (18-07-2014) for the FIT. But, once I get the figure, I want to keep it, because with the if function, once we are the 4th of July, the figure for C6 disappears...
Fri
18-Jul
FITGroup
Start
D-1503-Jul
D-1206-Jul
D-909-Jul
D-612-Jul
D-615-Jul
D-216-Jul
D-117-Jul
D18-Jul
So either a macro to copy the specific data into another sheet or a another way to keep it. Because the idea as the end is to do different scenario based on the result got per period..
View 2 Replies
View Related
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
May 23, 2014
We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
View 5 Replies
View Related
Jan 21, 2014
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
View 3 Replies
View Related
Feb 4, 2014
I have two sheets of data , sheet A and Sheet B.
Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.
I have filled out the first two rows of results that should be returned as an example.
One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.
View 3 Replies
View Related
Aug 28, 2012
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
View 5 Replies
View Related
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
Jun 10, 2009
Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.
View 10 Replies
View Related
Jan 15, 2013
I couldn't resolve the formula of Index-Match for getting the values that matches the following criteria
1) Match the "Category" of Table 1 to Table 2
2) Match the "value" of Table 1 to Table 2, if value is not present in Table 2 take the upper closest value.
Once the above conditions are met, Take the Result from Table 2
Table 1 is for Input and Table 2 is for Database.
Please note that Data in Table 2 should not be sorted in any way. I tried to use the -1 option of the Match function for upper closest value but it didn't work out.
Have a look at the file attached : IndexMatchUpperClosestValue_AlongwithExactMatch.xlsx
View 3 Replies
View Related
Oct 6, 2009
I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:
View 3 Replies
View Related
Sep 27, 2007
I am looking to sum the values found at the intersection of multiple values based upon a vertical and horizontal lookup. The formula I am trying is as follows (which results in #Value):
{=IF(ISBLANK(G$9),"",SUM(IF(INDEX('Journal'!$A$20:$Z$1020,MATCH("Prior",'Journal'!$F$20:$F$1020,0),MATCH(G$9,'Journal'!$H$19:$Z$19,0)),"")))}
View 9 Replies
View Related
Apr 6, 2009
Example:......
On my worksheet 1, I have two dropdown boxes: B7 (that gives the building name) and B11 which gives the Type of Commission (Renewal, Expansion, New). B 20 is supposed to automatically select the percentage based upon what B7 and B11 select. E.g. B7 = House, B11 = Expansion then B20 should be 3.
This is my formula.
=HLOOKUP(B7,'Building Details'!$A$2:$R$24,MATCH(B11,'Building
Details'!$A$2:$R$2,0))
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
Jan 8, 2014
I am using sheet 1 to pull data from sheet 2 using this formula
=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0)).
This on its own works to pull the data. However the data is repeated for each 'set' of data ie each group of repeating names, as I drag down. I just want the data to appear once on the first lie of each group so I have added an if to the formula
=IF(C2=C1,"No Match",
My complete formula is now
=IF(C2=C1,"No Match",=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0))
View 2 Replies
View Related
May 3, 2009
On the attached workbook, on the "Progress Summary" page, cell C30 has a formula which should list the names of pupils who made 0 progress (as referenced by the Raw Data page, column R [progress] and column A [name].)
View 3 Replies
View Related
Dec 3, 2009
I have never used the aforementioned functions before, but i assume thats what i need to do here....
In the attached table, what formula do i use to return the value '8' i.e. whats in column "Epsilon" and row "Three"
If you could let me have the generic formula i need as well, that would be good.
View 11 Replies
View Related
Feb 3, 2010
I can't figure out why the N/As are coming. In G2 I have =INDEX(Area_Codes!$D:$D,MATCH(F2,INDEX(Area_Codes!$B:$B,0),0)). If I replace F2 with 407, I get FL which is right. But why do I get N/A??
View 4 Replies
View Related
Feb 18, 2010
L26 returns incorrect information. Input parameters are F29 and F30. In its current state, it returns the value “b”, where I think it should be returning “1”, i.e the intersection point for “3Ph” & “Single-core 70°C thermoplastic non-arm Cu Table 4D1”
View 6 Replies
View Related
Mar 5, 2009
If the value on sheet2 columnA ,matches value sheet1, column AC
then corresponding row value sheet2,column E be put in corresponding row for matched value ,sheet1,column Z.
View 6 Replies
View Related
Mar 30, 2009
I have the following array formula that matches against a table with 3 criteria (one specfically is a time), it works ok until the time criteria is not of the value searched for, it may be +/- 30 minutes out, is it possible for the formula to index/match to the actual value or to find the nearest match possible, its in column B.
View 3 Replies
View Related
