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
ADVERTISEMENT
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
Jan 24, 2014
I have some data which I update every now and again. Then I extract and clean up and currently have it output to 4 columns.
The four columns currently looks like this:
A | B | C | D
1 name name2 stats stats2
2 ....
3 ....
Each column is then populated with a decent amount of names and stats, and the same name occurs multiple times. I'm looking up a specific name in the A column, and can extract the stats in C fine using INDEX/MATCH, but it only returns the data from the first instance the name occurs. I've googled it a bit and believe I could possibly wrap my head around returning multiple instances.
However, there are a couple of other criteria for my extraction of the stats which makes me believe I'm in way over my head. The data is in chronological order, with the newest on the bottom. I'm wanting to extract the stats in inversed order so starting from bottom going upwards, if that makes sense. Further, preferably I'd want to limit the amount of extraction to X amounts. And lastly to make it really complicated, for the newest entries stats are not yet available so only the names (column A and B) are populated, I need it to skip those entries, since, obviously, there are no stats there to extract, and I wouldnt want to end up with a lot of blank cells in my summary.
View 2 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
Apr 10, 2014
I attached a file containing two sheets. The first sheet is called Input on this sheet there are several categories of which I displayed the first three on. My ultimate goal is to get the Input per category broken down horizontally over the line-items on the Output sheet. Every category contains several line items to which costs are attached (f.e. in category 10, line item 4000, the costs are 33,000$ and so on).
The problem is that every month the costs are changing per line-item, but also the number of line items per category will change due to the fact that the line-items are only displayed if there are in a specific month costs booked to the line-item. In other words sometimes line-items are added due to costs booked at the line-item, but if in one month there are no costs booked at the line-item it disappears from the overview. A formula containing: '=if(category=10,vlookup(... is to static due to the changing line-items.
View 8 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
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
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
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
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
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 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
Feb 4, 2014
I have two sheets of data , sheet A and Sheet B.
Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.
I have filled out the first two rows of results that should be returned as an example.
One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.
View 3 Replies
View Related
Oct 22, 2009
I have a worksheet that has numerical data in A1:A22. In E1:E61 I have set values in numerical order and in F1:F61 I have a letter or letters that correspond to E1:61.
I am trying to write a formula in B1 that will look at the value in A1 and then match it up in E1:E61 and then return the corresponding letter in F1:F61.
e.g: in A1 i have an INDEX and MATCH formula
View 2 Replies
View Related
Apr 3, 2008
I have two worksheets that I am hoping to use to generate one report in excel. Sheet1 contains the following information for my entire work center:
Benefitor Account Dollar Value Document #
Sheet2 contains a list of Benefitors that are relevant to only me.
I want to pull the information from Sheet1 where the benefitors on Sheet1 match the benefitors listed on Sheet2 into Sheet3.
View 11 Replies
View Related
Dec 4, 2008
I have a tab that has Employee Name in one column and the benefit they chose in another column. The employee name is repeated a number of times since they have chosen more than one benefit.
I am trying to create a new tab where the employee name in is the first column (only once) and each benefit is listed in separate columns along the top. I want to put an "x" in the column for the benefit the employee has chosen.
Is there a way to say "look for this employee, see if this benefit is what they chose, and if it is, put an "x", if not, leave blank"?
View 9 Replies
View Related
Jun 5, 2009
I am trying to get the info shown on table 1 into table 2. I suspect index and match is the answer, but I am not sure.
Table 1 Employee # Deduction Amount 101 AD&D 7.00 101 LIFE 4.00 101 LTD 12.00 101 STD 6.00 101 HEALTH 300.00 101 DENTAL 25.00 105 AD&D 8.00 105 LIFE 6.00 105 LTD 6.00 105 STD 300.00 105 HEALTH 25.00 105 DENTAL 8.00 112 AD&D 7.00 112 LIFE 5.00 112 LTD 13.00 112 STD 5.00 112 HEALTH 450.00 112 DENTAL 37.00 Table 2 AD&D LIFE LTD STD HEALTH DENTAL 101 105 112
View 9 Replies
View Related
May 1, 2014
If I have a column of data with a bunch of values (which can't be sorted & which is constantly changing so cant be broken into another column and then sorted) what formula do I need to output the second occurrence of a value in that column?
View 3 Replies
View Related
