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


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

MATCH For Multiple Criteria While Ignoring Empty Array Cells

Nov 11, 2009

I'm working with a large amount of data (A21:BZ1503) and I'm trying to identify unique situations where any pre-defined combination of multiple columns in one row is flagged by producing a pre-defined value. For example:

I have my pre-defined criteria in worksheet 'X' hidden in my workbook -- note that there are many blank cells.

Worksheet 'X'ABCDE1Dept.CourseInst.Adj.2AGSM1.23MATH101Professor1.44ENGL1051.65ENGLProfessor1.86ETST1002

On worksheet 'A' the various users enter data -- each row is a unique group with data entered into the columns -- again note a cell can be blank:

Worksheet 'A'ABCDE1Dept.CourseInst.Adj.2AGSM110Professor3ETST2204ENGL108Professor5ENGL105Lecturer6MATH101Lecturer..............................

View 9 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

Match Value Of 1 Cell To Multiple Cells And Give Multiple Values

Oct 7, 2006

Sheet 2 is the problem.

I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.

Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.

I have included a sample.

View 9 Replies View Related

Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---


but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.

View 1 Replies View Related

Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:


Benefit Type
Delivered or Enabled

Saving of $4M over 24 months.


I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.


Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled


So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

View 1 Replies View Related

Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?

Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.



The attached example file may be a better alternative to view this problem.

View 2 Replies View Related

Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?

Feb 1, 2012

Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)

AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated

Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.

AAA blue round elongated
BBB orange
CCC smelly

View 3 Replies View Related

Fill In Table Based On Multiple Criteria

Jul 11, 2007

I would like to fill in a table based on a small database
this table then creates a graph.

But I need to look at 3 criteria before I can fill in the table
namely: customer - weeknumber - weekday

based on these items the time should be filled in accordingly.

Can this be done with regular formula or should I try VBA ?

please have a look at my file the data should go from sheet input to sheet ACL
based on the above mentioned criteria

View 4 Replies View Related

Sum Of Multiple Cells Within Multiple Sheets With Varying Criteria

May 5, 2013

I have a spreadsheet that has about 50 sheets and a summary sheet at the front the layout of the summary sheet is column A has skill sets from A4 to A20 and across the top it has week commencing dates from F3 to about BA3 (dates go from w/c 06/05/13 to 08/12/14). On the other sheets they have the dates in the same columns but the skill sets vary as to each project, some projects may have two of the same skill set in column A because there is two people needed who can do the same thing, I need to total up the days over the sheets for each week and skill set.

I have been using the SUMIF formula as follows:


And so on for each sheet and then change the formula for the next skill set and then again for the next column.

Is there a way of doing this without having to enter a formula into each cell, at the moment I am coping and pasting the formula into word to do a find and replace to change the SummaryA4 to SummaryA5 etc and then into the next cell for the corresponding date.

So to make this seem like it makes any sense I am looking for say an 'Administrator' in all the project sheets over all the dates and for it to add it up for the summary sheet. Under the corresponding week against the skill set.

So it would be F4:F20 for w/c 06/05/13, G4:G20 for w/c 13/05/13 and so on until all skill sets.

View 5 Replies View Related

Select Multiple Cells Based On Multiple Criteria?

Sep 25, 2013

I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.

In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.

I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.

View 1 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

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

Copyrights 2005-15, All rights reserved