INDEX With Multiple Criteria And Returning Multiple Rows But Only The Two Most Recent
Jun 10, 2013
I have a spreadsheet of prices (raw data), and I need to pull out both the most recent price for each item for a specific customer code for each item, as well as the second most recent price, (essentially the old price), for that customer code for each item.
I have columns for ITEM (code), PTC (Customer Code), PRICE, and EFF (Effective Date). For each item code, there are multiple prices for multiple customers, such as in the linked workbook.
I am currently using the formula, =INDEX(PRICE,MATCH(1,(F2=ITEM)*(G2=PTC),0)) to pull out the last price. This works because the last price is on top based on sorting. What I need is a formula that pulls out the next oldest price for that item/ptc combo. I tried using the SMALL function to no success.
what i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?
e.g. worksheet 1 has all the data
A B C
email ID date
worksheet 2 has a list of specific email addresses i'm looking for info on
A B
email date
My formula in column B of worksheet 2 is along the lines of this:
basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.
The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.
Index across multiple sheets returning multiple values
I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and the formula will return all the data related to this employee. I found this forumla to do this: =IF(ROWS(...)<=...;INDEX(INDIRECT(...);SMALL(IF(...=...;ROW(...)-ROW(...)+1);ROWS(...)));"")
But in my workbook, the data is on multiple sheets . How do I adapt this formula to look into 12 sheets?
that data would be returned in order of the date of the pay.
I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.
The only unique criteria I have to do this are customer surname and customer postcode.
In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.
If they do I'd like it to display "match" if they don't I'd like it to display "no match"
i have with noting the most recent date of a test and also the one prior to the most recent dependant on location and test type. I require most recent and previous test info to enable a comparrison to be made. All other dates are not required but need to be kept as historical data and can't be deleted.
Is there anyway i could get column E to auto populate and amend itself as more test dates and locations are added to the list?
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?
I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.
I need a formula that will find the column that meets two conditions:
1) Today's date 2) A specific time (referenced from a cell on the main sheet)
It must then pull the data from the cell in that column that is in the row marked "Total".
Here is an example of the data set from the web query:
I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:
This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.
This one returns the standard "#N/A" error message.
I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.
The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.
It may also be worth noting that the number of columns and rows in the data set is dynamic.
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
I'm trying to get a value to return based upon three criteria, sales rep, compensation plan and calculation method.
For each sales rep (there are 20 reps), there is an option for one of five comp plans and for every comp plan, there are six calculation methods (these six methods are the same for every one of the five comp plans).
I know I need to use the Indes(Match()) set-up, but I'm having the darndest time figuring out how to set up the source matrix to do this right.
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run. 2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L 3. In Column D, I want to specify 3 criterias namely A, B and C 4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo 5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
Attached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.
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)
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 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.
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
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
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.
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‎
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.
I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.
HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.
C is the part number column in the schedule K is the date row in the schedule ....
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.)
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.
