Excel 2003 :: Index And Match - Table With Header Row Of Scores
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 ... ?
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B C D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.
Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
I have 3 test scores for each student. Currently, I have 3 students.
For example :
John 95 90 85 Cindy 50 60 100 Dan 87 86 90
I have a chart that that plots 3 lines, one for each student.
However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.
I have a rather large table that I need to return the column heading from.
Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.
Using an example;
5% 10% 15% Bob 3.5 4.1 4.15 John 3.6 4.2 4.30 Chris 3.3 4.9 4.95 Sammy 3.7 4.6 4.7
Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.
I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.
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.
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?
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):
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.
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.
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.
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
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:
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
I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.
I'm using Excel 2007
The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)
The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .
It's just six columns of data. The short list is in AY from AY2 : AY15 The long list is in BB from BB2:BB505 The column header "AL" is in AY1 and NL is in AZ1
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.
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.
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.
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).
I have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.
So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.
I have a victim of the Index-Match duplication problem in Excel (2010). Basically, I have three columns of data, all daily input for the year.
Column 1 = Date Column 2 = Actual (Units Sold) Column 3 = Scheduled (Units Sold)
The Date is filled out through the end of the year as is the Scheduled values. The Actual values are filled out daily.
I need to generate a summary box that reports Actual, Scheduled, and Variance (Actual - Scheduled) for the time periods Daily, Month to Date, and YTD.
My problem is that when I try to return the Schedule value that corresponds with the date of the last entry, I don't know if I am pulling the correct Schedule value since I do not know if the Actual value (that is pulled from the last value in the Actual column) is unique. So I tried using an Index-Match formula to return the latest value (that is the last record occurrence of the value) to my function in order to retrieve the correct Schedule value, but, sadly, it did not work.
I by no means am an Excel expert like many of you, so I may have some questions along the way.
I've attached a sample extraction from my worksheet and included an example of the Summary panel I'm creating.
The context of this problem is that I am pulling sales data from different outlets to analyse monthly sales. The data from the various outlets are in various formats and the products, even though they are the same throughout all the stores, are named differently. For example, Mushroom Soup can be named as "Soup Mushroom", "Mushroom Soup" or "Cream of Mushroom". This makes analysis really tough.
I first created a table with a list (K6:P6) that had the varying names of the products. This is shown as the first table with 3 rows.
Then I tried to match this list to a range of products sold by an outlet e.g. Electric. (shown in the next bigger table $D$10:$D$254, ) Next, I used Index to call up and display the sales of that product next to table with list A ($G$10:$G$254)
This works for some entries but not for all. I get mostly #NA answers even though I do ctrl-shift-enter. Also, the bigger table is in a separate file and I am using excel 2010.
Whatever formula I try is returning an error of #N/A
I have two worksheets the first with transport data - customer,collection point, delivery point, haulier and number of pallets - all this information is in columns; the second sheet with the rates show haulier, customer, validity, delivery point in columns and then pallet numbers run across a row with rates tying in underneath. i have simplified this below.
I am just trying to reference all the data and work out what the cost for the variables would be in the example the cell i'm trying to find is H8 (320) -
I have already tried the following and several modified versions: =INDEX('RATE LOOKUPS'!$E$6:$I$9,MATCH(1,('MASTER DATA'!A:A='RATE LOOKUPS'!C:C)*('MASTER DATA'!B:B='RATE LOOKUPS'!B:B)*('MASTER DATA'!C:C='RATE LOOKUPS'!D:D)*('MASTER DATA'!D:D='RATE LOOKUPS'!A:A)*('MASTER DATA'!E:E='RATE LOOKUPS'!E5:I5),0))
I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).
When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.
**Why does it do that and is there a way to stop it?
Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.
I am using Excel 2010 on a company computer with the macros blocked so no VBA.
Rows 1 and 2 (range: A1:CM2) within Spreadsheet X contain the range of data that I need.
Within Spreadsheet Y, Row 6 will change monthly and will be input by the user. The value in Row 6 equals the data in Row 2 in worksheet X.
I need to put a formula in row 4 of spreadsheet Y that returns the value of Row 1 in Spreadsheet X.
Unfortunately, I cannot change the spreadsheet layout, otherwise I could do an HLOOKUP formula using Spreadsheet Y Row 6 and Spreadsheet X Row 2 and be done with it. I assume that I need to use an Index-Match formula, which I am not as familiar with. In addition, all of the online support I have found explains Index-Match with data in columns, not rows.
=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9 When the workbook with this formula is open it slows down other workbooks quite a bit.
If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.
How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.
I have two data sets across two worksheets. The first worksheet contains sales order numbers (Sheet1:column A) and other data . The second worksheet contains sales order numbers (Sheet2:column A) and the product details.
On sheet1, I had to manually duplicate a sales order number (inserting another row) if the number of units of the order is greater than 1. I then need to fetch the product details from sheet2 for each unit for that specific order number. However in sheet2, there are multiple products for one sales order number.
How do i create a lookup/match to fetch the product details for each sales order number without duplicating the product details if there are multiple products for one sales order number?
I know a simple vlookup function will return the values that it matches first and that is not what i want.
