Cell P14 = 4.7
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.
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
=INDEX('Data Dump'!$C:$C,MATCH('YTD Detailed'!B$2&A40,'Data Dump'!$G:$G&'Data Dump'!$D:$D,0))
I have to above array formula, i am looking to nest an offset within it. That looks up two columns to the right, but im having trouble.
Can an offset be added to an index?
I created an Index/Match array forumula in the worksheet. It works.
View 2 Replies View RelatedImagine 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:
{=SalesRow*INDEX(MarginColumn,MATCH(Sales,SalesColumn,1))}
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?
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.
with a multiple criteria index match array!
I have attached an example where I need to bring back a result matching 4 specific criteria, but I cannot seem to get it to work at all!
I have attached an example dataset with the formula that I was trying to get right (and failing miserably!!)
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:
Why this formula code:
Code:
=INDEX(INDIRECT($A$3&D7&$A$4),MATCH(B7, INDIRECT($A$3&D7&$A$5),0),5)
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.
=INDEX('[GON-JUN 2012.xls]gon-jun'!$G$10:$G$254,MATCH(K6:P6,'[GON-JUN 2012.xls]gon-jun'!$D$10:$D$254,0))
[URL]
complex formulas in Excel but managed to work out and create the following formula;
{=IFERROR(INDEX('owssvr(1)'!$AE:$AE,MATCH($A9&AK$1,'owssvr(1)'!$C:$C&'owssvr(1)'!$E:$E,0)),"")}
Basically I want to return a call quality score if the name and week match my criteria.
The above formula works perfectly, however, it is very slow and the sheet takes ages to update.
Is there a quicker and more dynamic way to achieve the same result?
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?
Here is the table I am working with
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.
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?
=INDEX(C2:C11,MATCH(MODE(COUNTIF(C2:C11,C3:C11)),COUNTIF(C2:C11,C2:C11),0))
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))
confirmed with ctrl shift enter.
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
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 am trying to find all the 0 in column P and return col M from the same rows, this finds the 1st, how do i find the rest?
=INDEX(M$3:M$100,MATCH("0",P$3:P$100,0))
I have a data table, called Table1 in Sheet1. In a simple form it is
Customer ID
Comment
A
asdf
B
jkl;
So in VBA, I need to figure a way to give me the cell address of the matching cell in Comments. So say Customer ID starts at A1 ... that means the Comment for Customer A is B2.
How would I get that reference in VBA?
I am struggling with a INDEX,MATCH and MAX formula. I am trying to retrieve values from column E with two criteria:
1. That the value in column C is equal to a lookup value
2. That the date in column A is the closest before date for a lookup date
I have attached an example file.
Is it possible to use Match to find a value within a Named range and then, based on that value, use Match and Index again to find a value two columns across?
I have a list of clients in Column A, with 10 cells between each. In Column B, I have a list of currencies (the same currencies next to each client) and in Column C the rate this client pays for this currency. I want to reference these rates from an external workbook. Is there any way to use Match to locate the client name, then use Index/Match to locate the rate for a particular currency, somehow telling Excel where to look the second time?
I'm using Index/Match to find a value in another workbook, but there is one value that I want to ignore. For example here is my function --
=INDEX(ZZANALYSIS_PATTERN.xls!$A:$H,MATCH(D8,ZZANALYSIS_PATTERN.xls!$G:$G,0),2)
In D8, the value is Blue. Here is an example with made-up values (X is meaningless values) --
A B C D E F G
X Apple X X X X Blue
X Pie X X X X Blue
When I use the function, I want it to retrieve Pie, but it always retrieves Apple. Is there a way to ignore Apple? I've tried throwing "Apple" in there a couple different ways, but nothing is working. Or, if I can search from the bottom up, that would work as well.
I have the following formula that returns a number, however it returns the first number it comes to when the criteria is met,
View 2 Replies View Relatedin one column I am looking up the value Yes to return the row number.
=MATCH("Yes",C:C,0) in this case it returns a 2
I want to use this row number in a sum...
i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.
I have to do a vlookup in 12 sheets (named ranges)
I use the formula '=vlookup(a1;choose(1;range1;range2; etc);2;0)
In this case I have to change the 'choose index_num' every time to match.
Is it possible to do the lookup without changing the 'choose index_num'?
I've used the below formula to fill column D with the name of the cheapest supplier for the parts listed in each row.
=INDEX($G$1:$M$1,MATCH(MIN(G3:M3),$G3:$M3,0))
Is there a way to fill columns E & F with the 2nd & 3rd cheapest suppliers?
My table is shown below:
Capture.PNG
I am trying to write a formula which finds the last instance of a number greater than 30 in a column (B). The values are not sorted from smallest to largest as they correspond to a time series (A) which needs to be preserved. Ultimately, I want the formula to return the time at which this value occurs, but I think I can do that bit using INDEX
I have a formula to give the first instance (row number) of a number above 30, which seems to work, but beacuse I don't fully understand how it works I can't modify it to give the last instance. The formula is:
=MATCH(1,INDEX(--(B2:B883>=30),0),0)
Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
This had been hunting me for weeks and I still could not come over it after weeks of study the conditional formating IF, match, index, Vlookup, etc to find several duplicated value, e.g Column B = Name 1 & Row B = Time and so on, where as Name will have data of Name 1 duplicated in different time, while row B, "Time"would change to a different Name and maybe it would go back to Name one.
My problem was dont know which formula I can use in a different sheet to bring back the data to Name 1 did Task A at Time A, while Name 1 did Task B at Time K, etc. If this is not Name 1, then Name B replace above.