Chronological Listing & Match Criteria From Multiple Columns
Jun 14, 2007
I cant get this one off the ground. Normally I am pretty good to find bits and pieces of advice from other posts and put something together, but this one has me stuck from the get-go. I want to create several lists based upon the row(s) containing at least one set of criteria (day and time, contained in two differnt columns). Each row allows for up to 3 sets of days and times (i.e. columns B and C, columns D and E, and columns F and G) Each row is date and time stamped as to when the data was entered in that row (column H).
I want one list for each set of the various day/time combinations allowed in columns B thru G. The list should provide the name(s) contained in column A, in chronological order based on column H, when a criteria match is made in columns B:C, D:E, or F:G. This is far easier to understand with the attached sample. The lower section of the sample represents my desired output that I cannot seem to achieve.
View 3 Replies
ADVERTISEMENT
Mar 17, 2009
Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.
I tried using sumproduct but It wont give me anything but error messages or a zero.
Looks like this
=SUMPRODUCT(A:A="Stewart",(G:G="Fiduciary"),C:C)
Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C
View 9 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Feb 28, 2012
I have a table with sales teams down the left, and products along the top.
The table shows how many sales of each product a team has made. This is populated from a sales data dump each day.
I need a second table with all the products along the top, and beneath each product, a list of which Teams have sold that product.
do I need some Index / Match style formula here to pickup up each team from the left in the first table that's got a sales value higher than 0 for a particular product?
View 5 Replies
View Related
Nov 21, 2012
I am using this formula but I don't know how to get it to populate more than one cell.
Here is the formula:
=INDEX(APPROVED!$A$3:$A$1000,MATCH($F$4&$H$1,APPROVED!$D$3:$D$1000&APPROVED!$C$3:$C$1000,0))
It has multiple matches but I can only see the first match in the cell.
View 1 Replies
View Related
Dec 2, 2013
I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.
The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).
And I want it to extract "Bills" value from "rule" sheet.
There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.
How do i solve this problem with index-match function?
View 3 Replies
View Related
Apr 11, 2008
I have a list of items (TR Sets) from 1 to 96 in the range B4:B99, with values corresponding to each one (Acid Number) in the range C4:C99. I would like to make a separate list that names each TR Set (using numbers 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?
View 13 Replies
View Related
Aug 10, 2009
This is driving me nuts. What I have is:
Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text
Sheet 2
ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values
What I want is in Column F (Sheet1) a formula to:
if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True
View 9 Replies
View Related
Jan 19, 2010
I have been trying for the last 2 hours to write a formula that does the following;
If a number appears in this table (on another worksheet named VAT Codes) then return VAT but if it appears in this table (on another worksheet named VAT Codes) then return NO VAT. The following formula returns VAT but N/A when it should say NO VAT; =IF(F3="","",IF(MATCH(F3,'VAT Codes'!$I$7:$I$19,FALSE),"VAT",IF(MATCH(F3,'VAT Codes'!$K$7:$K$143,FALSE),"NO VAT","")))
View 4 Replies
View Related
Mar 7, 2013
I am trying to return a reference to the first cell that meets several conditions. I can calculate a column (say Col E) that tells if each row meets condition with
=IF(AND(IF('number of cases by year'!B28:B547>'cumulative distribution >0 '!F51,TRUE,FALSE),IF('number of cases by year'!$A$2:$A$521>=DATE('% of cases captured'!$C$3,'% of cases captured'!$A$3,'% of cases captured'!$B$3),TRUE,FALSE)),TRUE,FALSE)
Then to find the first time this is met I use
=MATCH(TRUE,INDEX(E2:E521,0),0)
Is there a way to do this all in one step? I tried
=MATCH(TRUE,INDEX(IF(AND(IF('number of cases by year'!B20:B539>'cumulative distribution >0 '!F43,TRUE,FALSE),IF('number of cases by year'!$A$2:$A$521>=DATE('% of cases captured'!$C$3,'% of cases captured'!$A$3,'% of cases captured'!$B$3),TRUE,FALSE)),TRUE,FALSE),0),0)
but it does not seem to work. It returns #VALUE
View 1 Replies
View Related
Jan 28, 2014
I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.
There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).
Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.
Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.
View 1 Replies
View Related
May 23, 2014
I am making a table that reads from an list of employees. I have attached a sample sheet (changed the names). The list includes Name, 2014 Start Date, Pay Type, Job Title, Location, Weeks, Hours, Avg. Hours.
I need a formula that will list employees on a separate sheet based on the following criteria:
2014 Start Date = 1/1/2014
Avg. Hours >=30
I have worked with a number of INDEX MATCH combinations but I cannot get the formula to quit at 30 Avg. Hours whenever I drag the formula down. I also have seen some employees that average over 30 hours get "skipped" over whenever I drag the formula down.
View 5 Replies
View Related
Mar 8, 2009
On sheet one, I am trying to match the employees job code for that particular week in cell K2. The vlookup/match is trying to find the particular job code for that employee number for that particular week ending.
Cell A2 (sheet 1, shows their employee #),
Cell J2 (sheet 1) has that particular week ending.
Cell K2 has this formula in sheet 1:
=VLOOKUP(A2,Sheet2!A:C,3,MATCH(J2,Sheet2!$D$1:$D$5,0))
Sheet 2
Column A = employee numbers
Column C = job codes (which I need to appear in cell K2 for sheet 1)
Column D = has the week endings to match against column J in sheet 1
View 2 Replies
View Related
Oct 7, 2013
I am trying to create a formula to pull in the mgmt fee% into the investor capital forecast tab, based on two vaiables. The client's AUM(column B), and their Tier (column A). It should pull in 1.157% from the Mgmt fee schedule tab, based on AUM of 314MM, and Tier 3.
indexmatch.xlsx‎
View 3 Replies
View Related
Aug 1, 2012
I am trying to do an index match with multiple criteria and it keeps on returning a 'False' value. I am also not sure if it is adjusting the indexmatch lookup values for each row.
Code:
Sub Check()
Set ws1 = ActiveWorkbook.Sheets("SprocketPartData")
Sheets("SprocketPartData").Activate
Set ra = ws1.Range(Cells(2, 4), Cells(65536, 4).End(xlUp))
Dim c As Range
[Code] ........
View 7 Replies
View Related
Dec 15, 2013
I have 2 workbooks and would like to use an index match formula to populate the data in the second workbook. I wanted to populate TOTAL HOURS in workbook 2 by matching "Month To Date" and "Employee ID" from Workbook 1.
Workbook 1
A
B
C
D
E
F
1
Supervisor
First Name
Last Name
Employee ID
Total Hours
[Code] ...........
View 9 Replies
View Related
Jul 1, 2014
I need to write a formula using Index Match that is looking at to criteria field. I've done this formula before with one criteria field but now I need 2 and the way I'm writing it isn't working.
=(INDEX('Agent Weekly DataSheet'!G:G,MATCH('Agent Weekly Overall Scores'!B8,IF('Agent Weekly DataSheet'!F:F='Agent Weekly Overall Scores'!G8,IF('Agent Weekly DataSheet'!B:B='Agent Weekly Overall Scores'!C8,'Agent Weekly DataSheet'!B:B),0))))
The bolded piece is what I can't figure out.
View 2 Replies
View Related
May 4, 2009
I have a couple of desired outcomes.
1. I want to find the “Close” price based upon “Date” and “Time” input (search criteria).
2. I want to know when (what time) a “Price” (input) falls between the “High” and “Low”, on a specified date.
DateTimeOpenHighLowCloseVolumeTimeDateTimeCloseTime2/24/200913:00138.21138.23138.18138.22410.022572/24/200913:00#N/ABUY2/24/200913:01138.23138.30138.18138.26680.02260SELL2/24/200913:02138.25138.28138.21138.28340.022632/24/200913:03138.27138.37138.27138.35680.022662/24/200913:04138.36138.41138.31138.33440.022692/24/200913:05138.34138.44138.32138.39330.022712/24/200913:06138.40138.45138.37138.41350.02274
View 9 Replies
View Related
Apr 19, 2006
I'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows
=INDEX($C$2:$Q$51,MATCH($B2,$A$2:$A$51,0),MATCH(G$1,$C$1:$Q$1,0))
and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX($C$2:$Q$51,AND(MATCH($B3,$A$2:$A$51,0),MATCH(D3,$B$2:$B$51,0),MATCH(G$1,Telesales!$C$1:$Q$1,0)))
View 2 Replies
View Related
Oct 29, 2008
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
View 9 Replies
View Related
Jun 9, 2014
Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:
EnglishSS1 EnglishSS2 EnglishSS3 Rating
10 20 30 3
20 30 40 2
30 40 50 1
Where my variables would be based on whether a person is under EnglishSS1 and getting a score of <=10, the resulting rating would be 3. I know this is doable by doing a vlookup with a range lookup value of false. However I have roughly 70 column headers and it will be a bit taxing to combine an If and Vlookup statement to address it.
I have attached a sample sheet for reference : Book1.xlsx‎
View 2 Replies
View Related
Jun 22, 2014
with a multiple criteria index match array!
I have attached an example where I need to bring back a result matching 4 specific criteria, but I cannot seem to get it to work at all!
I have attached an example dataset with the formula that I was trying to get right (and failing miserably!!)
View 6 Replies
View Related
Dec 3, 2013
In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.
In cell B12 i have an index match that I can not get to return a value.
Is Index Match the correct method of returning a result? Or have I just got the formula wrong?
View 1 Replies
View Related
Feb 9, 2010
We're selling language courses that have a different price depending on when a course is taken. We're trying to build a price quote application that needs to look up a certain school and course and price driven by a start date.
How can I build a lookup function that says: pick the price of school X and course Y when the start date falls between dd/mm/yyyy and dd/mm/yyyy?
I manage to build a look up function with MATCH and INDEX when the condition of start date is exactly matched but dont know how to instruct it to match a value between a start and end date.
View 14 Replies
View Related
Oct 26, 2011
I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.
=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))
Code reflects the ALL GRADES data below.
TERM SUBJECT NUMBER CRN CREDITS GRADE ID
1 Biology 301 12345 4 B 123456
2 Biology 302 23456 4 B 123456
3 Biology 303 34567 4 A 123456
3 Biology 338 65432 4 C+ 234567
3 Biology 338 54321 4 A 345678
1 Biology 336 43210 5 B+ 456789
1 Biology 337 43210 2 A 456789
1 Biology 338 65432 4 B+ 456789
1 Biology 301 12345 4 A 567890
2 Biology 302 23456 4 A 567890
3 Biology 303 34567 4 A 567890
Row Labels Min of TERM Count of GRADE First grade All grades
123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!
345678 3 1 A A #NUM!
456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11
* Need all grades where TERM = Min of Term
View 7 Replies
View Related
Dec 29, 2011
The below formula was provided by PGC, and works great:
=INDEX($E$15:$E$18,MAX(IF($A$2:$A$8=A15,MATCH($E$2:$E$8,$E$15:$E$18,0))))
In post:
Complex Array(?) Search
Now, I would like to add the ability to perform this same action/concept, but using two different criteria.
So I want to keep the hierarchical listing of importance, but lookup/match within using more than one criteria.
Example:
I have an order of superseding to apply to results of a search for Fruit.
Great
Good
Fair
Poor
(so Good supersedes, Great; Fair supersedes, Good; etc.)
Column A____Column B____Column E
Apple_______Red_________Fair
Apple_______Red_________Poor
Apple_______Green_______Great
Pear________Red_________Great
Pear________Yellow_______Great
Pear________Red_________Good
Pear________Red_________Fair
I want my verification to return all the worst Red fruit:
For Apple Red: Poor
For Pear Red: Fair
View 1 Replies
View Related
Feb 5, 2013
Is it possible to use an Index/Match formula that looks at multiple criteria?
View 1 Replies
View Related
Dec 14, 2013
When I enter my formula on the same sheet the data is on, I get the answer I am looking for, but when I enter it on a separate sheet, I get #N/A
Here is the formula on the sheet that contains the data: (works perfect)
=INDEX(K1:M144,MATCH(H17&I17,K1:K144&L1:L144,0),3)
Here is the formula on the separate sheetreturns #N/A)
=INDEX(List!K1:M144,MATCH(D4&E4,List!K1:K144&List!L1:L144,0),3)
View 5 Replies
View Related
Feb 22, 2014
I have two different workbooks. In workbook1 I have a table like below:
A
B
C
[Code]....
What I want to do is to create a drop down menu in workbook2 where I can select a name
and then see below what time that person is working each day of the week.
View 1 Replies
View Related
Jun 20, 2007
I am trying to get my INDEX & MATCH formula to retreive data from my table.
This is what I can do so far:
Jan-07Feb-07
100 12
250 45
=INDEX(table,MATCH(B13,balance),MATCH(C13, date))
But I am trying to get it to get another row to look up as well.
I want it to look up the color then the 100 or 250, then the date.
Jan-07Feb-07
red100 12
red250 45
blue100 78
blue250 1011
=INDEX(table,MATCH(B13,balance),MATCH(C13,date))
I think i need to insert another match code in the row section but cant seem to get it to work.
View 7 Replies
View Related
