# Match 2 Criteria With 2 Criteria To Pick Up A Value?

Dec 12, 2013

I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?

Item
lot
Amt
Items
Lot #s
Yds

10429.50.0
N49950
2.5
0201.019FLAME.0
T37530
9

[code]....

ADVERTISEMENT

## Compare Two Cells In A Row With Criteria. Count Rows That Match Criteria

Sep 22, 2009

Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.

I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference

## Provide Filter With A List Of Criteria But When It Doesn't Match All Of The Criteria?

Apr 1, 2014

Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?

E.g i have this code

ActiveSheet.Range("\$A\$7:\$N\$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues

However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?

## Pick Value From By Count Criteria?

Apr 3, 2014

At the moment I have a problem as per example attached, that I need to select values from row and populate respective column based on the following.

1. A top row contains non-negative values

2. Rows below it contain blanks, negative values and positive value.

3. Last column to be populated with a value from the top row if the value in the rows below top row contains the last negative value before the first positive value. There could be blanks between the last negative and the first positive, but I want the last negative to be used as a criteria for selection of corresponding value from top row. I hope my question is clear with the attached example. The column to be populated is FY.

## Match 2 Criteria..?

Mar 4, 2009

Col B is a range B2:B200 Col C is a range C2:C200 Col D is a range D2:D11, Col B contains any number from 1-10 Col C contains any number from 1-100 Col D will contain the sum of the occurences when the value in C equals 98 or 100. For instance if in Col B the number 9 occurs 17 times and the corresponding cell in col C is 98 or 100 for 6 of those times then in D10 I would hope to see the value 6. Thus far my hopes have been dashed to pieces against an insurmountable wall of error codes!

## Match 3 Criteria

Mar 11, 2009

Match 3 criteria. I'm a relative newbie to coding having a bit of difficulty with the following formula:

## Match Either Of Two Criteria (not Both)

Dec 19, 2012

Let's say I have the following situation:

Column A has values 1-15, each repeated many times. I want a match function to return all the row numbers for which column A is a 3 or a 4. (It's within an index function (which is with a standard deviation function), so not sure how I could match twice and not break my higher level nested functions).

## SUMPRODUCT To Match Criteria

Jun 3, 2014

I am trying to match 3 values, and return a matching 4th from a chart. It will first look at one value and see if it falls between values (the LOAD value in cell B1..it will look to see if it falls between the "low limit" and "high limit" in columns A10 to A135 and C10 to C135, respectively. Then it will look for a match for the values in cell A2, and in cell A3. the return value will index to the corresponding value in column G10 to G135. I have written a formula to do this in cell D2....but it will only ever return the value in cell G10.

See the attached excel file : nozzle tester.xlsx

## How To Use The INDEX And MATCH Against Two Criteria

Jul 8, 2014

Screenshot1.JPG

Screenshot2.JPG

So basically, I have these two sets of data in 2 separate tabs. I need the 2 sets of data circled in green that match to bring up data circled in red.

How would I go about doing this?

## Return A Value If 2 Other Criteria Match

Jan 11, 2009

Rather than attempt to describe my problem here and risk cofusing people on what I want to achieve I have put a diagram together. I think this is the best way to illustrate my problem.

Diagram is available here
[url]
There is also a copy of the document available here for any body willing to take a look.
[url]
Please bare in mind I need this doc to be compatible with the 2003 version of Excel.

## 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","")))

## Match Certain Criteria To Product?

Dec 21, 2012

I tried to use SUMIFS and VLOOKUPS to resolve the problem, but it didn't work out too well.

## Match 3 Criteria / Count Once

Dec 4, 2012

I'm looking for another twisted forumla / array. Here's what I'm trying to do?

When the value in data!G\$2:G\$51000 = Yes
AND
The value in data!J\$2:J\$51000 is Unique (counted once if more than 1 total matches)
AND
The value in data!H\$2:H\$51000 is

## Match Tow Criteria With Total

Jun 4, 2013

I am trying to create a summary sheet in excel and i am in need of a formula which gives me a summary in Sheet 2.

i.e. Match column A2:A19 with F2:F19 and give me the total in Sheet 02 with date and reason.

Sheet 01

In no
In Date
In Sector
STA
ATA
Out
OutDate
Out Sector
STD
ATD
Total
Reason

[Code] .......

Expected result in Sheet 02

Flt No
Date
Reason
AB318
AB300
AB030
AB420
AB484

[Code] .....

## Get Address With Match Criteria

Dec 21, 2009

what is the cell address whith column criteria MATCH to:
column1 = "A"
column2 = "B"
column3 = "C"

AND...

with header criteria MATCH = "ZZZ"

## INDEX & MATCH 3 Criteria

Mar 12, 2009

I have a userform that collects production data from several production lines each running up to 4 shifts every day. This populates a table for performance related calculations to be applied.

Each day I must produce a report for each line and shift showing the performance stats I have calculated. the format of the report has been defined for me and I'm not allowed to change it at the moment.

I wrote a line of code to be applied to each cell I wanted to show the data in excel 2007 but this doesn't work in 2002 which is the version in our office and I'm hitting my head against a brick wall.

Essentially the report will either use Now() or get the user to enter a date, then I need to search for the unique reference that meets the date, the line and the shift and return several values in the report from the row in my "daily data" table.

Here is the code I have so far - it returns a "VALUE" error...

=INDEX('Daily Data'!A6000:AA6000,,19,MATCH(\$B\$2&"Line "&\$A\$6&B6,'Daily Data'!\$F6000:\$F6000&'Daily Data'!\$H6000:\$H6000&'Daily Data'!\$I6000:\$I6000,0))

(although it doesn't show ot here I have entered it as an array formula)

The previous code I tried (again entered as an array) returns a "NUM" error

=INDEX('Daily Data'!A:AA,,19,MATCH(\$B\$2&"Line "&\$A\$6&B6,'Daily Data'!\$F:\$F&'Daily Data'!\$H:\$H&'Daily Data'!\$I:\$I,0))

B2 is date (dd/mm/yyy)
A6 is line number (numeric)
B6 is shift (alpha)

## Match Criteria And Return Value From Different Row

Feb 22, 2007

I have 2 rows of data and want excel to find the number of times that a number appears in the first row and then return the value of a cell in the same column but in the second row of data. I need it to repeat this until all matches in row one, and their corresponding number in row 2 have been found and then add all the results from row 2 together to give a single numerical answer. I have tried the ' lookup' function but this only returns the first number that matches the criteria and does not continue to find the remaining matches.

## 3 Criteria Find & Match

Feb 16, 2008

I have three sets of columns with addresses. I'm looking for a match/index/vlook up formula that can search all three columns and list the matched addresses in another column. Also note that one or two of the columns are formatted differently (although there is enough information to match closely to the other columns where relevant).

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

=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

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

## Referencing A Second Criteria Within INDEX And MATCH

Feb 7, 2014

I've attached a sheet with the problem.

I have one sheet which summarises the status of documents in another sheet. I'm using INDEX and MATCH to return the correct Document title and info. But I need to offset/countif/INDEX MATCH again or something to get to reference the latest revision status of the document.

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

## Look Up That Would Show All The Results That Match Certain Criteria?

Feb 14, 2007

I have a spreadsheet that holds salary information. At the moment it shows against each person the highest salary for a person of the opposite ***. What I would like to do is have another cell that shows the names of all people who have that salary.

E.g. if the highest comparable salary for someone of the opposite *** is 30000 then I would want a list of all the people with that salary.

Is it possible to do a look up that would show all the results that match certain criteria?

## 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:
=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

## Offset Match (two Criteria Lookup)

Aug 7, 2009

In the attached workbook, the following formula appears in column B of the "dropdown" tab. I want to basically do a two criteria lookup, using the values in column A and the value of cell B1, which is a dropdown based on a dynamic range consisting of the company names on the Discounts tab.(starting with cell E1, extending rightward) To make this easier for testing purposes, I'm curretly using cell E1 instead of B1, which is just text.

It seems like this ought to work, but somehow it doesn't:

=OFFSET(Discounts!\$B\$1,(MATCH(\$E\$1,Discounts!\$C\$1:\$AA\$1,0)),(MATCH(A5,Discounts!\$B\$2:\$B\$200,0)),1,1)

## Averaging Numbers If They Match A Criteria

Feb 16, 2010

I am running Excel 2003. I have two columns of numbers (column A and column B)
column A has 1s and 0s. column B has latencies (ie. 100-500ms). When column A has a 0, i would like to know the latency of the following row in column B. from this i would like to average all of these latencies that are found. for example.

column A has 1,1,0,1,1.
column B has 100, 200, 300, 400, 500.
so since A3 is 0, then i want the latency of the following row in column B which is B4 or 400. and then average these findings.

## Using Index And Match For Two Criteria - One In A Column Other In A Row

Dec 20, 2012

How to get the following index/match formula to work.

I have 2 criteria that I need to find the result of in a table that has the one criteria down the column, the other across a row at the top.

The formula I have is thus far:

{=INDEX(\$K\$46:\$AV\$46,MATCH(L98,IF(\$K\$15:\$AV\$15=N98,\$K\$27:\$K\$82),0))}

In this L98 is the criteria I'm in theory doing a vlookup against the values in column \$K\$27:\$K\$82, and N98 is the theoretical hlookup against row \$K\$15:\$AV\$15, bringing back the intersection point from row \$K\$46:\$AV\$46

All I get is #N/A .....

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

indexmatch.xlsx‎

## Match Criteria According To MINIMUM And MAXIMUM

Nov 27, 2008

I need a macro that will search MIN and MAX columns and identify which horse has the maximum number in both the MIN and MAX columns an example is highlighted yellow in the attached spreadsheet. If there is a selection i want the macro to keep the horse that matches the criteria along with the race date and time e.t.c (header) but delete all other horses in that race. If there are no selections that meet the criteria in a race i want it to delete all details for that race including headers and go to the next race.

## Delete Rows That Match Criteria

Feb 2, 2009

I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.

Copyrights 2005-15 www.BigResource.com, All rights reserved