Match Two Criteria And Return Multiple Rows
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
ADVERTISEMENT
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
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
Nov 28, 2012
Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.
I would like to be able to get a list of all values in a given column that match a specific name in another column.
Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.
For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.
The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.
CPT Sample Book.xlsx
View 4 Replies
View Related
May 13, 2014
I have a large data set which is basically just transactions on accounts by date and I want to return the transactions in date order by account number. I've attached an example where sheet 2 is the transaction listing and sheet 1 is what I'd like to return.
View 3 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
Jun 20, 2008
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.
EG
Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50
View 9 Replies
View Related
Jun 28, 2013
I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.
Create_List.xlsx
View 4 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
Jan 11, 2009
Rather than attempt to describe my problem here and risk cofusing people on what I want to achieve I have put a diagram together. I think this is the best way to illustrate my problem.
Diagram is available here
[url]
There is also a copy of the document available here for any body willing to take a look.
[url]
Please bare in mind I need this doc to be compatible with the 2003 version of Excel.
View 14 Replies
View Related
Feb 22, 2007
I have 2 rows of data and want excel to find the number of times that a number appears in the first row and then return the value of a cell in the same column but in the second row of data. I need it to repeat this until all matches in row one, and their corresponding number in row 2 have been found and then add all the results from row 2 together to give a single numerical answer. I have tried the ' lookup' function but this only returns the first number that matches the criteria and does not continue to find the remaining matches.
View 7 Replies
View Related
Sep 22, 2009
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
View 5 Replies
View Related
Apr 3, 2006
I would like a Formula to match 3 criteria in the following order:
1. Numeric Reference (NOT Unique)
2. Numeric Values ( NOT Unique) - lowest value
3. Numeric Label (Unique)
The Numeric Reference that I'm looking for will vary (Input Cell).
Search /Match ALL specified (duplicate) References.
From the specified References Return the Numeric Label that has the "LOWEST"
Numeric Value .
Data Layout is 3 Rows:
1st Row E4:AC4 Numeric Values (NOT Unique)
2nd Row E5:AC5 Numeric Labels (Unique)
3rd Row E6:AC6 Numeric References (NOT Unique)
Sample Data:
E4:AC4 Numeric Values (NOT Unique) 145 127 120 160 130 170 160 160
E5:AC5 Numeric Labels (Unique) 20 21 22 23 24 25 26
27
E6:AC6 Numeric Reference 8 0 8 2
0 10 8 30
Scenario:
Looking for Numeric Reference 8, the relevant Numeric Labels are 20, 22 and
26. Their respective Numeric Values are 145, 120 and 160. The Numeric Label
with the lowest value of 120 is 22.
Expected Result:
Numeric Label 22
View 14 Replies
View Related
Feb 14, 2014
I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:
If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.
2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE
View 3 Replies
View Related
Nov 5, 2011
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
View 3 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Nov 21, 2012
I am using this formula but I don't know how to get it to populate more than one cell.
Here is the formula:
=INDEX(APPROVED!$A$3:$A$1000,MATCH($F$4&$H$1,APPROVED!$D$3:$D$1000&APPROVED!$C$3:$C$1000,0))
It has multiple matches but I can only see the first match in the cell.
View 1 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
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Feb 2, 2009
I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.
View 5 Replies
View Related
Dec 29, 2007
I am trying to write a code to copy rows from sheet 1 to sheet 3 if cell of column A "account #" on sheet 1 are matching cell of column A (account #) on Sheet 2. If multiple entries find on sheet 1, (e.g. acct # 101c shows twice) copy all matched rows to sheet 3 as well.
View 3 Replies
View Related
Aug 22, 2013
I want to write a macro that match multiple column and return a value as I show in the table below
Plant
SLOC
Grade
Posting Date
1
555
abc
[Code] .....
I have to check multiple column from 2sources and get the Posting Date from Table2 to Table1. I'm quite confused about the function which I should use like match, lookup, index, etc.
View 4 Replies
View Related
Jan 19, 2010
I have been trying for the last 2 hours to write a formula that does the following;
If a number appears in this table (on another worksheet named VAT Codes) then return VAT but if it appears in this table (on another worksheet named VAT Codes) then return NO VAT. The following formula returns VAT but N/A when it should say NO VAT; =IF(F3="","",IF(MATCH(F3,'VAT Codes'!$I$7:$I$19,FALSE),"VAT",IF(MATCH(F3,'VAT Codes'!$K$7:$K$143,FALSE),"NO VAT","")))
View 4 Replies
View Related
Nov 10, 2009
I need to retrieve information from multiple rows in a table based on certain criteria (date compared to today's date, existence of "no" in a column).
View 6 Replies
View Related
Feb 7, 2008
i have a speadsheet with 2 columns
1 good work
2 good
3 excellent
4 needs improvment
i need a macro that goes through each row and does a search in column B for a partial match of a word. if that word isnt present in the row, the row is deleted.
if i typed in a criteria of "good"
rows 3 and 4 are deleted.
so my speadsheet will then look like this
1 good work
2 good
View 9 Replies
View Related
Feb 21, 2007
My knowledge of Excel is quite basic, so i had little luck searching for this one.
We have a main data sheet in which everything is inputted, but i would like that data to be split up and reported (copied into) in 2 or 3 other sheets, depending on a single criteria.
Is this able to be done so these 'report' sheet are constantly updated, and the columns in the reports dont include irrelavant columns which are in the main data sheet.
View 14 Replies
View Related
Jun 19, 2007
I have a file which has some data I want to keep and some data I want to strip out.
The data I want to keep will always have either a date or a string “Overdue” or a string “> 1 year” in the first column, anything else I want to remove.
Example file shown below
23-Apr-0812341234123412341234
30-Apr-0812341234123412341234
19-May-0812341234123412341234
27-May-0812341234123412341234
> 1 year12341234123412341234
Overdue12341234123412341234
View 9 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
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
Jan 28, 2014
I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.
There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).
Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.
Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.
View 1 Replies
View Related