Imagine 5 periods of sales, growing by $550 per year, from $250 to $2,250. There is a columnar table with sales in increments of $500 to $2,000 paired with margin percentages.
Using INDEX(MarginColumn,MATCH(Sales,SalesColumn,1)) successfully retrieves the correct margin % for each period where Sales is that year's sales.
So, I tried to construct an array formula in the following way:
Sales is not changing, so that only the 1st year's margin percentage is being applied to each year's sales. I confirmed this by extending the array to a multi-cell layout.
How can I correct the formula so that the margin percentage is looked up as if it were using each year's sales independently? I know I can just SUM the array once this works, but this is my current road block.
P.S. If you see this answer immediately, how might I multiply the percentages by the sales that increase by $500 and sum the whole expression into one cell?
I'm using the following formula to look at a range of cells and return the most common text entry. The formula works fine as long as there is text; if there are more blank cells than entries, then it returns a 'blank' and my formula cell is empty. How to clean this up so that it ignores blank cells?
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 am trying to understand how to use index and match in an array formula. Probably easiest to take a look at my example sheet. For some reason, the first result is working, but the others aren't.
[URL] .....
Alternatively, here is a screenshot.
Uploaded with ImageShack.us
The formula I have tried is: =INDEX($E$3:$E$11,MATCH(1,IF($A$3:$A$11=G3,IF($B$3:$B$11=H3, IF($C$3:$C$11=I3,IF($D$3:$D$11=MEDIAN(J3,K3,$D$3:$D$11),1,0) ,0),0),0),0))
My array is H42:N72, in H42:H72 I have numbers 15,14,13....0,-1,-2,-3, etc...
In N42:N72 there is a corresponding $ amount that I would like to return based on finding the value which is greater than 4.7 (so, looking to match the $$ amount to the number 5 in column H42:H72)
I've tried using INDEX MATCH but can't figure this out.
Solution for all: {=SUMPRODUCT(SUMIF(Sheet1!$A$2:$A$16,$F$34:$F$40,INDEX(Sheet1!$B$2:$K$16,0,MATCH(T$10,Sheet1!$B$1:$K $1,0))),U34:U40)}
Say I have an Excel workbook with two sheets: Employee Hours and Employee Wages. On the Hours sheet I have 31 columns (Jan1 - Jan31) with inputs for the number of hours worked by each employee by day. On the Wages sheet I have listed the same 31 columns with their wages each day (I do this to factor in wage increases that occur fairly often).
My goal is to use an INDEX/MATCH function to calculate the total daily compensation for each day on the Wages sheet by multiplying hours*wages for all employees that day. Here's the code I'm using:
Where A3 D7 and A4 concatenate to form a workbookname!named range1 and equally A3 D7 and A5 form workbookname!named range2 works perfectly well for a fixed range as named range, but not if the named range is dynamic using the typical OFFSET, COUNTIF method?
I need to be able to concatenate the range reference in this way, but for the named range reference to be a dynamic range that will grow and change with additions etc.
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.
How to do INDEX MATCH ARRAYS. (to populate my report I need to match multiple rows and columns from source sheet to import data).
Now I am trying to replicate same in VBA. (for this example row1&2 & column1&2 on both seed(source) and result(one I am trying to populate) sheets).
I wrote the code below that works just fine for 1 CELL.
Sub Button1_Click() Range("C4").FormulaArray = "=INDEX('SEED'!$A$1:$f$6,MATCH(A4&B4,'SEED'!$A$1:$A$6&'SEED'!$B$1:$B$6,0),3)" End Sub
I know in excel I can simply drag the formula across rows/columns to populate them automatically and the way I would do this in VBA would be by creating loops.
Here is what I have that needs improvement
Sub Button1_Click() Dim iRow As Integer For iRow = 3 To 4 Range("C" & iRow).FormulaArray = "=INDEX('SEED'!$A$1:$f$6,MATCH(AiRow & iRow,'SEED'!$A$1:$A$6&'SEED'!$B$1:$B$6,0),3)" Next iRow End Sub
Here iRow is to identify row number, and to keep simple I am only doing 2 rows. but how do i write MATCH statement to identify rows needed to be matched from SEED sheet?
Date Open High Low Close Volume Adj Close Semi-Annual Yield Theoretical Bond Price Index Coupon Total Value Peaks Troughs
[Code] ..........
The Relevant columns would be "Date" column A cell 1, "Total Value" column L cell 1, "Peaks" column M cell 1, and "Troughs" column N cell 1. What I basically want to do, in the next column, column O, is this: If cell N# = "Trough" find the previous M cell = "Peak" and take the L cell in that "Peak" row and subtract the L cell in the Trough row from it (like =(L4-L9)/L4)). Then in column P, if the percentage drop was 5% or greater, I want to find the days between the trough we are looking at, in cell n10, and the NEXT Peak, cell M11, using the corresponding dates in Column A.
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?
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 currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I am trying a simple Index or Array formula and have been trying all day to get this work to no avail. The data is freight costs for shipping different size containers (20' and 40') to different ports (Lyndhurst, Port Coquitlam and Seattle).
I will have the port name plugged into one cell and the container size plugged into another cell and want to pull the corresponding freight cost out into another cell. The red text in the file are the data that I will plug in. The box below is where I would like the result.
I know I can do this as I have already succeeded once but since moving a few things on my spread sheet the formula has gone wrong and I can't correct it.
I have one sheet with many different lines on and another I would like to select certain rows with the same reference number in column A. Below is the formula I am using
Current I am using two spreadsheets for similar press and am combining the two. What I need help with this time is currently in cell c9 the formula is =ROUNDDOWN(17.7/C7,0) or =ROUNDDOWN(39.3/C7,0) depending on which press I am using. The presses are shown in cells d34 - q34. Indigo =17.7 gilboa = 39.3. I have added these numbers to a table in the scales tab a24:e26. Is there a way to write a formula in cell c9 on the input tab to combine the 2 above formulas and insert the correct 17.7 or 39.3 when required?
Guys im stuck on the fact of how to pull the correct information through for a named person.as from the example if i place say steve in the named box i would like all of steves scores to appear in the designated boxes.and so on.
I have attached a formula I have used to extract a value from another sheet (I have also attached where the formula is mean't to extract the value from) . My problem is that this formula is not returning what it should?
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.
