Nested Index / Match Array - Return Value In Column C When Matching Column A And B But With Few More Criteria
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
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.
Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.
The Make-Models tab has the lists used to create the drop-down selections.
The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.
Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.
2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.
Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.
I am currently looking at a simple data set spanning a few years of annual average data. I want to create a front sheet to show people in what year the maximum value was reached for each site but am having trouble returning the values......
The summary sheet is headed:...... I want the formula to say - find the summary sheet max value cell in the main data sheet row 2 (for XYZ) and return the corresponding column from row 1 (year). I've got myself in a muddle trying to create array data from the index function and want something simple. There are only about 30 rows so am happy to manually select the rows in question and repeat the formula entry so it doesn't need to be fancy.
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
I have an array of data with multiple codes stored in text...
I want to set a formula in a cell to look for all instances of specific text in an array. Then compare the value of all of the column headings, which are numbered across the top of the array, which contain a match in that column and return the highest value.
Example: 10x10 array, columns 1,3, and 5 contain "text" somewhere in those columns. Formula would return 5.
I would imagine that the title of this thread made little sense, so let me try to explain:
Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.
So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match 2) In column "G", sum up all the values in "F" that go with the matching values in column "A" 3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
I need to find a way to find an array in a tab to use for an index/match function I have.
this is what i would normally use: =index(tab_array,match($a2, tab!$a$1:$a$1000,0),match($B$1,tab!$a$1:$zz$1,0))
This formula would usually work fine when I know that within the tab, the array in which the row match is searching doesn't change. However, that array may change in the future, to say column H, without my knowing as it is a database that gets automatically populated from an upstream system.
Is there a way to search for an array within an index/match?
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
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)
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
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D. e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
I would like to amend my macro w/ additional code that will allow me to view the value in column A, if a subject is chosen from list from columns D or E. Choosing a singal subject to view from row2,from column,D or E will cause the value in column A to not be seen if the subject does not correspond with the row the value is located. Is there a way to match values from corresponding/matching column values with the rows with values in columnA? And. allow if match found to display/list the value in the first empty cell in column A for match? I would also like the macro to reverse match for column A.
So if a single value is selected in column A for view, then all rows for which the value pertains to will also be shown, instead of just the single row with the value. Here's an examplary make of the booklet.
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.
I'm working on a project in which I have 50 states + DC listed by 2-letter abbreviation, and want to return a value in another column that will be a non-unique number (some will duplicate in the second column.)
Also need to do this same operation with first column (state) and third column, which also is comprised of non-unique numbers.
Hlookup, lookup & pivot tables seem to not be a fit for this.
I have two excel files. File #1 is a varying length and has 4 columns with ID #s in Column A. File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B.
I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2.
I've tried using the substitute function but I could only replace one found ID at a time.
I am having a little trouble with tying an index/match formula that would probably require an "if" portion to the formula as well.
If you take a look at the attachment, I'm trying to match column "G" to column "L" and then use the appropriate price according to the specified piece count in column "H" based on the table to the right.
I have three worksheets the first one called Econ the second one called CheckLogs and the third is a customer sheet. I am needing to match up a cell from the Econ sheet with a cell from the Checklogs sheet and then it matches a cell from the customer sheet with a cell from the checklogs to determine what customer sheet. My problem is how do I loop through the customer code cell until it finds the correct cell.
so in the formula "B1" is located on the customer sheet and is the customer code field, "CheckLogs!B2" is on the CheckLogs sheet and is the customer code field. I'm just not sure how you loop if the customer code in B1 doesn't equal the customer code in CheckLogs!B2 and run through the formula again.
I am trying to write one Excel formula that can accomplish the following: Review a Discount Sheet to determine whether a Class Name should be purchased based on its current discount compared to my Target Discount for that Class Name. The majority of the time, weekly discount changes occur in 5-10% increments, but occasionally they will occur in increments of 20% or more. If a discount percentage increases by 20% or more and surpasses my Target Discount, the current method I am using to automate this process does not work.
Example: Last week's discount in cell G2 is 25%, and this week's discount in cell H2 is 40%. The Target Discount for that class is 30%, so under my current system I would not request that Class Name for purchasing because the current discount does not exactly equal my Target Discount, even though the previous week's discount did not exceed my Target Discount and now I should purchase that Class Name because the discount has gone from not meeting to exceeding my Target Discount.
INDEX MATCH used to retrieve current discount: =INDEX('Discount Sheet'!$H$2:$H$4910,MATCH(A3,'Discount Sheet'!$F$2:$F$4910,0))
Validity Test in Cell F2 to compare Current Discount to Target Discount: =E2=C2
NOTE: The above validity test could include a >= to capture Current Discounts that have exceeded the Target Discount; however, over time all Current Discounts will exceed the Target Discount. It is only when last week's discount did not exceed the Target Discount and now the Current Discount does exceed the Target Discount should a class be purchased.
The final result of this formula should be "TRUE' or "FALSE" without having to cut and paste any data for additional validity comparisons.
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
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?
I have used this technique before with a sumif formula, to use the column that matches a reference cell but I cannot get this to work on a sumifs formula. What I have used previously in a sumif formula (only 1 criteria) is below.
Basically, I want a code that does a simple index-match function for a column - But for all the #N/A's that come up in that column, I'd like it go to another INDEX-MATCH function - and another after that. I'm trying to keep it all in one column.
