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

Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

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

VLookup Or Index / Match Multiple Occurrences?

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

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

View 1 Replies View Related

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.

View 5 Replies View Related

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‎

View 3 Replies View Related

VBA Multiple Criteria Index Match

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

INDEX / MATCH And Multiple Criteria

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

Index Match With Multiple Criteria?

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

Index Match - Multiple Criteria

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

Multiple Index Match Formulas Instead Of Static If Vlookup?

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

Index Match Array Multiple Criteria?

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

Index Match Formula For Multiple Criteria?

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

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 View Related

Multiple Criteria On Index / Match With Hierarchy

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

Index Match Formula With Multiple Criteria

Feb 5, 2013

Is it possible to use an Index/Match formula that looks at multiple criteria?

View 1 Replies View Related

Index And Match With Multiple Criteria From Different Sheet

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

Index & Match Formula: Multiple Row Criteria

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

Index Match - Multiple Criteria And Multiple Matches

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

Using Wildcards For Text In Index / Match Multiple Criteria In Excel

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

Index & Match Multiple Items/Criteria: Finding The Nth Occurrence

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

Extracting Data Based On Multiple Criteria - Index Match Array?

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

Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately

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

VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??

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

VLOOKUP / INDEX And MATCH To Return All Values That Match

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

Vlookup OR Index And Match.

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

Match/index/vlookup

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

Index Vlookup Match

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

Vlookup Or Index/match

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

VLOOKUP / INDEX And MATCH For Second Value In Column?

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







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