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).
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
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?
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]....
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!
View 3 Replies View RelatedMatch 3 criteria. I'm a relative newbie to coding having a bit of difficulty with the following formula:
View 4 Replies View RelatedI 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
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?
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.
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","")))
I tried to use SUMIFS and VLOOKUPS to resolve the problem, but it didn't work out too well.
View 7 Replies View RelatedI'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
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] .....
what is the cell address whith column criteria MATCH to:
column1 = "A"
column2 = "B"
column3 = "C"
AND...
with header criteria MATCH = "ZZZ"
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)
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.
View 7 Replies View RelatedI 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).
View 2 Replies View RelatedI 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
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.
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.
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.
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?
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
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)
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.
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 .....
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
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.
View 2 Replies View RelatedI would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.
View 5 Replies View Relatedhttp://www.excelforum.com/excel-gene...-criteria.html target="_blank">Color Cells Which do not match criteria
i m having a problem similiar to the above.I tried using the nested ifs in Conditonal formatting but i m getting errors.Can anybody complete my formula
