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


SUMIF Multiple Conditions (two Columns Match Criteria Add The Third)

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

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

Search Multiple Criteria If Match Append Multiple Cells To One Cell?

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 #.


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:

C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health

View 5 Replies View Related

Listing Teams That Have Sold Products - Index / Match?

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

How To Use Match And Multiple Criteria To Fill Multiple Cells

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:


It has multiple matches but I can only see the first match in the cell.

View 1 Replies View Related

Index Match - Multiple Criteria And Multiple Matches

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

Listing Cells That Meet A Criteria

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

Match Two Columns Across Sheets And Based On Criteria Of Another Column

Aug 10, 2009

This is driving me nuts. What I have is:

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

Use Multiple MATCH Criteria?

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

Multiple Criteria Index Match?

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


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

Formula To Sum Multiple Match Criteria?

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

Index Match And Multiple Criteria

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

VLook Up Match Multiple Criteria

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:

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

Index Match Multiple Criteria

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.


View 3 Replies View Related

VBA Multiple Criteria Index Match

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.

Sub Check()
Set ws1 = ActiveWorkbook.Sheets("SprocketPartData")
Set ra = ws1.Range(Cells(2, 4), Cells(65536, 4).End(xlUp))
Dim c As Range

[Code] ........

View 7 Replies View Related

INDEX / MATCH And Multiple Criteria

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



First Name
Last Name
Employee ID
Total Hours

[Code] ...........

View 9 Replies View Related

Index Match With Multiple Criteria?

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

Index Match - Multiple Criteria

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.


View 9 Replies View Related

Match Multiple Criteria From Different Arrays

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


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

Delete Multiple Rows :: Match In Multiple Columns

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.


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

Index / Match Or VLookup For Multiple Criteria

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

Index Match Array Multiple Criteria?

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

Index Match Formula For Multiple Criteria?

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

Using MATCH And INDEX Function With Multiple Criteria?

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

Match Two Criteria And Return Multiple Rows

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.

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

Multiple Criteria On Index / Match With Hierarchy

Dec 29, 2011

The below formula was provided by PGC, and works great:


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.

I have an order of superseding to apply to results of a search for Fruit.
(so Good supersedes, Great; Fair supersedes, Good; etc.)

Column A____Column B____Column E

I want my verification to return all the worst Red fruit:
For Apple Red: Poor
For Pear Red: Fair

View 1 Replies View Related

Index Match Formula With Multiple Criteria

Feb 5, 2013

Is it possible to use an Index/Match formula that looks at multiple criteria?

View 1 Replies View Related

Index And Match With Multiple Criteria From Different Sheet

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)


Here is the formula on the separate sheetreturns #N/A)


View 5 Replies View Related

If Multiple Criteria Match Send Data

Feb 22, 2014

I have two different workbooks. In workbook1 I have a table like below:



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

Index & Match Formula: Multiple Row Criteria

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:
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.
red100 12
red250 45
blue100 78
blue250 1011

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

Copyrights 2005-15, All rights reserved