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
ADVERTISEMENT
Jun 11, 2014
I've been trying to edit my index formula to only count an order number once even if the criteria for that result appear more than once. Attachment 324723
View 7 Replies
View Related
Sep 26, 2011
I am trying to do index match function with criteria ranges in another worksheets. The code kept on giving me " #Value!"..
Here is the code:
Sub test1()
Dim myvalue As Variant
Dim wsname As String
Dim i As Integer
Dim j As Integer
Dim r1 As range
Dim r2 As range
Dim r3 As range
Dim r4 As range
wsname = "CustomerIDbyParts"
[Code] .......
The resulting cell of F5 kept on returning #Value!.. I suspect that my syntax for looking up the ranges from another worksheet is wrong?
View 3 Replies
View Related
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
View 1 Replies
View Related
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
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‎
View 3 Replies
View Related
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.
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
[Code] ........
View 7 Replies
View Related
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
A
B
C
D
E
F
1
Supervisor
First Name
Last Name
Employee ID
Total Hours
[Code] ...........
View 9 Replies
View Related
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
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.
DateTimeOpenHighLowCloseVolumeTimeDateTimeCloseTime2/24/200913:00138.21138.23138.18138.22410.022572/24/200913:00#N/ABUY2/24/200913:01138.23138.30138.18138.26680.02260SELL2/24/200913:02138.25138.28138.21138.28340.022632/24/200913:03138.27138.37138.27138.35680.022662/24/200913:04138.36138.41138.31138.33440.022692/24/200913:05138.34138.44138.32138.39330.022712/24/200913:06138.40138.45138.37138.41350.02274
View 9 Replies
View Related
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
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
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
Dec 29, 2011
The below formula was provided by PGC, and works great:
=INDEX($E$15:$E$18,MAX(IF($A$2:$A$8=A15,MATCH($E$2:$E$8,$E$15:$E$18,0))))
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.
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.)
Column A____Column B____Column E
Apple_______Red_________Fair
Apple_______Red_________Poor
Apple_______Green_______Great
Pear________Red_________Great
Pear________Yellow_______Great
Pear________Red_________Good
Pear________Red_________Fair
I want my verification to return all the worst Red fruit:
For Apple Red: Poor
For Pear Red: Fair
View 1 Replies
View Related
Feb 5, 2013
Is it possible to use an Index/Match formula that looks at multiple criteria?
View 1 Replies
View Related
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)
=INDEX(K1:M144,MATCH(H17&I17,K1:K144&L1:L144,0),3)
Here is the formula on the separate sheetreturns #N/A)
=INDEX(List!K1:M144,MATCH(D4&E4,List!K1:K144&List!L1:L144,0),3)
View 5 Replies
View Related
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:
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.
View 7 Replies
View Related
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
Nov 21, 2005
I have a worksheet that has 6 columns of numerical data that all column
datasets are referenced together for each row. So for example the data
in row 5 in all colums is related.
Column C through F have 4 numbers that refer to the data in the first 2
columns. These are also ordered by row.
I need to look in column A for 0.00, and column B for 3.14 Both of
these must be in the same row or I need the closest value. When these
values are found in columns A & B, return the values from the same row
in Columns C through F.
View 13 Replies
View Related
Jun 10, 2014
I'm having trouble using wildcards for text in index/match multiple criteria; I need to find the nearest site along a river (x) below a certain point (distance =21), each stretch of the river is labelled with the streches downstream (so stretch abc is upstream of ab).
My problem arises when the nearest point downstream is on the downtsream stretch of river. How do I use wild card to search for ab&"*" that will exclude abd.
River
Distance
Stretch
S1
x
10
a
[Code] ..........
'formula in cell E14 ={INDEX(A2:C11,MATCH(1,(A14=B2:B11)*(C2:C11
View 2 Replies
View Related
Jun 25, 2008
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
View 2 Replies
View Related
Jun 4, 2014
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula...
=INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
View 12 Replies
View Related
Jan 2, 2013
I have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.
Column 1 Lists the various factories
Column 2 Lists the Machine
Column 3 Lists the Part
Column 4 Lists the result.
Results can either be "ok", "warning", "Alert" only
View 6 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Jul 16, 2012
I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.
I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.
I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.
View 4 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
Aug 28, 2012
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
View 5 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
Dec 3, 2013
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
View 2 Replies
View Related
Jan 21, 2014
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
View 3 Replies
View Related
