Using SMALL And INDEX (MATCH) In A Table
Feb 16, 2014
I have a Pivot table (see picture) and in another field I want to be able to type in the Month and Year (e.g. January 2012) and return the worst performer of the 8 suppliers. I have been trying to use SMALL in conjunction with INDEX(MATCH(MATCH)) but it doesn't appear to be working.
I am sure there is a simple solution but it is evading me at the moment.
excel.jpg
This is where I am at with the formula. Maybe I can use the IF function somehow?
=INDEX('Pivot Tables'!B19:I19,MATCH(Summary!G3,'Pivot Tables'!A20:A41,0),
MATCH(SMALL('Pivot Tables'!B20:I41,A5),'Pivot Tables'!B20:I41,0))
View 4 Replies
ADVERTISEMENT
May 15, 2014
I have an equation below that work great except when row 2 has a duplicate value. When this happen row 5 only return the first value in row 1 and not the other duplicated values.
=INDEX($B$1:$AF$1,MATCH(SMALL($B$2:$AF$2,COLUMN(B$1)-1),$B$2:$R$2,0))
see attachment. How can I have row 5 return the following value: B5 = A, C6 = B and D5 = C.
View 2 Replies
View Related
Jan 18, 2014
I'm using this formula to lookup data based on the lowest average.
[Code] .....
The problem is that the minimum average is a two way tie, and I need to return both data sets. It just happens to have the same average.
I've tried using SMALL(array,1) and SMALL(array,2) in place of MIN above, but it gives me the same data set. I've also tried resizing using offset.
See attached : sample.xls
View 6 Replies
View Related
May 19, 2014
Here is a code for me to print the names in ascending order even if values are of same means it is showing one name only instead of the other name
means if values are A=1 B=2 C=3 D=4 E=2
it has to show like this A B E C D
But the result is coming like this A B B C D
Formula which i used
=OFFSET(B$6,MATCH(SMALL(C$6:C$18,ROW()-ROW(C$6)+1),C$6:C$18,0)-1,0)test.xls
Here i am attaching the sample file for your reference
View 10 Replies
View Related
Jan 12, 2009
I have a table with the months from B1:M1 & Customers from A3:A35. B3:B35 are transaction numbers. On another sheet, I have a cell, C2 with the name of the month that I type in, I've named it MONTH. I also have a seperate section for each customer that NBVC has helped me populate from another sheet based on MONTH and their actual transactions. At the bottom of the section, I want it to populate from this other table based on the month in the MONTH cell.
View 2 Replies
View Related
Jan 19, 2009
I have a number of identicle tables, that have all been seperatly named. On a summary tab i am using an index match formula to pull back key pieces of data from the table that is identified by cell B2. So if in B2 their is SCB, i would like the formula to apply to the table SCB. The formula (when useing the word SCB works fine):
View 2 Replies
View Related
May 20, 2009
Need in index and match function from a pivot table. I have enclosed an attachment with the sheets that I use. "Data" - contains the data, "Pivot" is the pivot from the data, and "Output sheet" is the sheet where the cells need to be populated.
Basically what I would like to do is to take the pivot and then match the currency, the time indicator and thereby also fill in the value for the matched cell. I have been racking my brain how to do this and I fail miserably.
View 2 Replies
View Related
Jan 8, 2013
My VBA currently reads a weekly updated table and basically dumps it to the end of a masterfile table...however I need to add a small section that will then go back to the beginning and line by line copy one cell in a single column to the same table but to the previous year.
i.e.
G8:G12345 contains this weeks sales which have already been added to the bottom of the masterfile (along with lots of other data)
A8:A12345 contain a barcode
B8:B12345 contain a date
What I now need to do is lookup the barcode and Date -1 year (so 2013 becomes 2012) and write the same data to column AG(n) for 2012. I could use formulas to lookup the data in the Master Table but I am trying to keep this table free of any formulas.
It's the match and index, and write bit I'm struggling with, looping through the inital data is not causing me any problems.
View 6 Replies
View Related
Feb 13, 2013
I regularly use the Index/Match formula. I just noticed, when indexing a table of subtotals, the match doesn't necessarily pull in the subtotal, but rather pulls in the first instance found in the rows that have been subtotaled. Is there a way to index/match the subtotal table to force the match to only view the subtotals, not the rows grouped to create the subtotal? I know I can manually, copy/paste the subtotals to "remove" the grouped data from the index range, but was hoping for an easier solution.
View 3 Replies
View Related
Jul 27, 2014
Look at the image I've attached.
I'm trying to have the table on the right match the first two columns to display the customer name taken from the table on the right.
testtable.jpg
View 13 Replies
View Related
Aug 25, 2014
I have a workbook (workbook1) with multiple sheets (sheet a, sheet b, sheet c), all with the same column headings. One of the columns in each sheet of this workbook contains an invoice number.
In a different workbook(workbook2) I need to find the invoice details based on sheets a-c from workbook1.
So, in workbook2 I can input the invoice number in column a and the rest of the details will be pulled through based on whichever sheet (a-c) from workbook1 that the details are in.
Workbook1 is normally closed (from reading others posts, the INDIRECT function might do what I need but would not work with workbook1 closed)
Working with just 1 sheet in workbook 1, the following formula works perfectly:
=INDEX('[workbook1.xlsx]sheet a'!$V:$V,MATCH(A2,'[workbook1.xlsx]sheet a'!$W:$W,0))
How to do the same, but looking in sheet a, b and c at the same time for the answer?
View 2 Replies
View Related
Aug 19, 2008
I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed
View 9 Replies
View Related
Feb 1, 2010
I'm creating a report that pulls certain record entries from another worksheet (Activity) in the same book. Originally, it only pulled one type of activity. "SO". Now I need it to pull "SO", "CC" and "BT" type activities into the same report. Currently my formula looks like this and works fine for only looking for the "SO" type:
View 2 Replies
View Related
Oct 4, 2013
Excel 2003
I have a table with a header row of scores 1-10 in B4:K4.
In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.
What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...
This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?
View 9 Replies
View Related
Dec 10, 2008
I have the formula (found in cell "C2") on the Report sheet. I need to perform a function, but I cannot get it to work on the sheet I need to pull information from. The sheet RecapWk12 has a small section pasted (with some cells edited for obvious reasons) from the actual workbook. I can get the formula in Report cell (A10) to work on pulling information from sheet2. You can see I am getting (#REF!) in cell C2.
View 3 Replies
View Related
Dec 9, 2009
Please see the attached worksheet for details. I would like the array function to search for instances of the word "FALSE" in column E and return the values of columns A:D when a match is found. I have done this successfully when the lookup value is a value in the first column of the range, but cannot seem to do so when the lookup value is in the last column of the range. I have received a #NUM! error each time.
View 9 Replies
View Related
May 10, 2012
I compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.
Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.
IF(ISERROR(INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2)),"",INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2))
Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.
These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.
The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.
What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.
View 4 Replies
View Related
Mar 26, 2009
I have 2 sheets recording feedback and summarising the overall percentages for each trainer.
My boss wants me to do this by month!?!?!
I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages
So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
View 7 Replies
View Related
Jun 25, 2014
I'm struggling with match, index and lookup functions. I have a formula in column D that calculates the percentage of answers against the total score for that category. The way it calculates enables me to delete rows if they are not needed and still provide a total count and a percentage score.
Now the percentage is returned from a dynamic data set, I want to plot the percentages on a spider/radar chart. Instead of manually creating the chart or using a pivot, I wanted to lookup the percentages (column D) against the category (Column A) in question and plot so the chart updates as the user changes the answers.
View 3 Replies
View Related
Jan 7, 2010
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
View 2 Replies
View Related
Sep 15, 2014
I have one data tab which constantly changes. (adding more products and corresponding volume/prices) For the lookup tab, I want to look up one value and return multiple values.
I have figured out an index array formula but it just calculates so slowly. My live file is about 8MB now. When I apply my formula, it takes forever to save.
View 7 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
Jan 21, 2010
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies
View Related
Mar 23, 2009
I have a data dump, on this single excel sheet their are a large number of tables, up to 100 in all, and what i need to do is extract key figures to form a summary table which i then produce stats for.
What i need is a macro or code that will automatically scan the tables, retrieve the key figures and return them in my summary table.
Attached is an example, you will see three small tables labelled Servicing query complex, servicing query simple and total.
I need to extract the blue cell, being the name of the table, the purple cell being the last figure in column c in each table (total number or processes) and the green figure being the the average turnaround. The final figure is i think the hardest to get as its the total number of figures in column c that have either a 0 or a 1 in Colum A. Note that their will be table where neither 0 or 1 will appear.
All figures are dummy figures but the table structure is correct.
View 14 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
May 23, 2014
We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
View 5 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
Aug 28, 2012
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.
2011
2011
2011
2012
2012
2012
[code].....
View 5 Replies
View Related
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
