Return Rows That Fit Criteria Via Index / Match?
Nov 6, 2013
I have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.
I need INDEX/MATCH to return the correct values for the subset below.
Column A (Forecast Status) - Column B (Account Name)
Commit - Account01 (Row 1)
Commit - Account02 (Row 2)
Won - Account03 (Row 3)
Won - Account04 (Row 4)
Won - Account05 (Row 5)
Upside - Account06 (Row 6)
Lost - Account07 (Row 7)
Upside - Account08 (Row 8)
Won - Account09 (Row 9)
Commit - Account10 (Row 10)
In a new tab, I am creating a 'dashboard' view that will group each of the following:
Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10)
Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9)
Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8)
Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)
View 1 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 11, 2010
I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student.
I've attached a mock report up so that maybe it would make more sense.
So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas.
Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs.
So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach.
Find the next instance Name & # matches, and pull that into rows 12-16, etc.
View 4 Replies
View Related
Jun 7, 2014
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
View 2 Replies
View Related
Oct 26, 2011
I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.
=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))
Code reflects the ALL GRADES data below.
TERM SUBJECT NUMBER CRN CREDITS GRADE ID
1 Biology 301 12345 4 B 123456
2 Biology 302 23456 4 B 123456
3 Biology 303 34567 4 A 123456
3 Biology 338 65432 4 C+ 234567
3 Biology 338 54321 4 A 345678
1 Biology 336 43210 5 B+ 456789
1 Biology 337 43210 2 A 456789
1 Biology 338 65432 4 B+ 456789
1 Biology 301 12345 4 A 567890
2 Biology 302 23456 4 A 567890
3 Biology 303 34567 4 A 567890
Row Labels Min of TERM Count of GRADE First grade All grades
123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!
345678 3 1 A A #NUM!
456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11
* Need all grades where TERM = Min of Term
View 7 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
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
Jun 25, 2009
I have two active worksheets. The first one is called 'Chart' and the second is 'Values'. I have three columns of data. I would like to look in the first two, and return a value in the third. =INDEX(Values!$C$1:$C$299,MATCH(Values!$D$1:$D$299,LEFT(Chart!A1:A16091,6)&" "&LEFT(Chart!C1:C16091,2)),0) .........
View 2 Replies
View Related
Jul 1, 2014
I am very competent using the index match formula and have incorporated this into many spreadsheets to auto-fill for avoiding input errors. The objective I am attempting now is to retrieve from a list of 2800 entries the smallest return on an index match formula.
From the data below if my Index Match formula uses C1 to index and match my return is always a7 (-12.7) the first in the list, where my desire is a return of a19 (-14.11). To complex this problem I often have duplicate amounts that need to be listed when they fall into my parameters. I have figured that the Small function allows me to assign first smallest, second smallest, and so forth.
I prefer not to use vlookup for simplicity and error avoidance issues.
a b c
1 amount Reason for Movement 2250
2 -183.252150
3 -366.612150
4 -61.1 2154
5 -91.652150
6 -4.34 2450
7 -12.7 2250
8 -2.98 2250
9 -2.98 2250
10 -69.372452
11 -0.6 2451
12 -4.49 2250
13 -4.49 2250
14 -4.14 2250
15 -4.7 2250
16 -4.55 2250
17 -4.85 2250
18 -1.41 2250
19 -14.112250
20 -8.47 2250
View 13 Replies
View Related
Aug 4, 2014
How can I modify this formula so it retrieves the 1st matching value (like always), but then the 2nd match, 3rd, etc.:
{=INDEX($A$2:$D$6,MATCH(F2:$F$6&G2:$G$6&H2:$H$6,$A$2:$A$6&$B$2:$B$6&$C$2:$C$6,0),4)} sheet attached: Example.xlsx
View 3 Replies
View Related
Feb 12, 2010
how to go about this, as I always get confused with Index and Match. In my example, in col A I have the Beginning of a period, Column B is the End of the Period, and the amount to be matched is in Column D. Range A1 to E4 is the source data. Below that, starting from A9 to A37 are dates. I would like to match the corresponding value in range D2:D3 if the date in range in A9 : A37 falls between the period stated in A2:B3. I've listed how the values should appear in C9:C37.
View 2 Replies
View Related
Nov 10, 2011
What I want to do is, given the data in col. a, I want to supply a number(range) of strings which can be found in column a, whenever a string is found, which exists in given range, I want to write the string from the Key(col. c) in col b instead of the value from the string that matches a value.
See below for example of what I got working but not exactly what I was looking for
Col. A- Data
Orangegreen
Blackwhite
Blueyellow
Col. B - Result
Col. C - Keys
Orange
White
Yellow
I used
Code:
=INDEX(A1:A3,MATCH("*"&C1&"*",A1:A3,0))
This does produce a result, but not entirely what I am after.
This requires only one string to search for, and will in cell B1 result in a value of 'Orangegreen' as I can only supply one valeu to the function. What I want to do is supply the function with a range of values, and have those values outputtet in colb instead af the value of cells where the result is found.
I tried modifying the function to include a range, but that does not produce a valid result.
View 5 Replies
View Related
Dec 20, 2011
If the weight of a shipment is between a range e.g 3.5kg I need it return the higher cost. For England this would be 9.31.
COSTLook upWeight KGEnglandScotlandIrelandWales1131-3 (KG)7.687.687.688.144454-5 (KG)9.319.319.319.4566306-30 (KG)14.3014.3014.3014.4531315031-50 (KG)22.4222.4222.4223.5551517651-75 (KG)35.1735.1735.1737.04767610076-100 (KG)45.2645.2645.2647.62KGS3.5ORIGINEnglandCost
View 9 Replies
View Related
Feb 17, 2013
In column B I enter a date when a student attended tutoring.
In column C I enter the name of a student attending the tutoring.
In cell D4, I will be entering a student name
In cells D5:D55 I'd like a formula that transposes the dates (if any) that a student attended tutoring. So if a student has their name appear beside the dates ... 14-Feb, 22-Mar, 24-Mar .... then I'd like those three dates to appear in cells D%, D6 and D7.
How to formulate this ... I'm guessing it's going to involve an index-match formula.
View 1 Replies
View Related
Jun 22, 2009
in one column I am looking up the value Yes to return the row number.
=MATCH("Yes",C:C,0) in this case it returns a 2
I want to use this row number in a sum...
i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.
View 9 Replies
View Related
Jul 8, 2014
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?
View 2 Replies
View Related
Mar 12, 2009
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)
View 3 Replies
View Related
Apr 3, 2014
I have an array formula which is working as expected returning the value of column K (offset by -1) if a number of criteria are matched.
[Code].....
The issue I'm having is there are occasionally duplicate values in column W and it's using the first value in its return, which isn't always correct.
Unfortunately due to the limited matches available the only way I can think to identify the correct value is by adding an additional criteria, which is that the correct value would always have a negative in Column X one cell above and to the right of where the value is being retrieved from. If there was no negative it would ignore this result.
Is there any way to add an additional criteria to the formula to stipulate that it must match a negative in column X but one cell above?
View 7 Replies
View Related
Feb 25, 2009
make an adjustment to my Formula so that if the Index match locates a cell that is blank it returns a blank cell rather than a 0. I know we need to add in =IF(iserror( but i am aving trouble..
View 5 Replies
View Related
Nov 27, 2006
I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!
I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status
plus a whole bunch of other columns of information.
On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.
I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.
Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.
View 12 Replies
View Related
Jun 16, 2009
I have a workbook that uses several indentification fields one for Job ID, One as a subID which occurs for every part of a job, i am currently using formula
View 5 Replies
View Related
Aug 26, 2009
I have a list of data that I want to average the number in column B when there is a match in column A
COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500
What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.
View 4 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
Feb 7, 2014
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.
View 8 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
Dec 20, 2012
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 .....
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
