I'm tryig to find a way to index data in an array that meets certain matching criteria. I am looking for an employee's rate on a given day by searching a database that lists the dates that an employee's rate was changed. I was hoping to solve it with a crafty index and match array formula but have been unable to find something that works so far. I have attached a simplified example of what I am trying to do.
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))
I need entering multiple arguments in an IF formula in Excel 2013. Here is the formula with just 1 argument.
=IF(SUM($G$4:G15)>3000,G15*0.8,0)
I need to combine it with the following.........
=IF(SUM($G$4:G15)>10000,sum($G$4:G15)-G15,0)
So to say, if the sum totals more than 3000 but less than 10000, then I want the cell value (G15) multiplied by 80% (.80). if it is greater than 10000, i want the sum minus 10000. If both arguments are false (sum totals less than 3000) then the value placed should be 0.
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 to sum the data in "details sheet" column H into "monthly sheet" based on the period and type match.
I am not sure where I am going wrong as the column references returned values that they should. However, the values in Col H from details sheet are not being summed up based on the criteria into monthly sheet.
I have the following array formula that identifies the first number in the column I:
=INDEX(I1:I1200,MATCH(TRUE,ISNUMBER(I1:I1200),0))
This formula works fine however I was hoping to expand the range to the entire column. I thought (obviously incorrectly) that the following formula would work:
=INDEX(I:I,MATCH(TRUE,ISNUMBER(I:I),0))
Instead of getting the same result as the first formula, I am getting "#N/A".
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?
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 used a Fuzzy lookup to match the data that was shared between my two tables. I was able to run 3 different Fuzzy attempts to increase my accuracy because the source data had a lot of spelling errors and such.
Now I am at a point where I need to analyze the 3 possible Fuzzy matches. I am kinda stumped on the best route to do this and thought that an approximate match Hlookup would be the best route but I can't seem to get it to work.
Here is a pic of my table:[URL]
I am trying to match the cell outlined in red against the 3 cells outlined in green, when the best suited match is found I need to copy the green cell and the orange adjacent cells to it's left and right (ID# and Similarity). It would be best if the match threshold was in the low 90% range.
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'm working with a large amount of data (A21:BZ1503) and I'm trying to identify unique situations where any pre-defined combination of multiple columns in one row is flagged by producing a pre-defined value. For example:
I have my pre-defined criteria in worksheet 'X' hidden in my workbook -- note that there are many blank cells.
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 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 run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.
I have a number of array formulas that refer back to the table in the top left. The array formulas are in Columns I and L, and cells N200, N203, and P203.
Normally when I add a new line to the top left table (usually by clicking on the empty box below the previous final line and typing the date), the table expands by one row and the array formulas all adjust to match, i.e. all the A180/C180s in the formulas become A181/C181s, etc.
However every once in a while (maybe once a month), the arrays simply refuse to update, and I can't figure out why. When they fail, they all fail, it's not hit or miss. You'll see in the attached sheet that though I've added a line to the table (A181) and data to that line, all the arrays still read through A180/C180.
Why this randomly fails to update, and if it's something I'm doing incorrectly?
And that leads to an associated question. In the past when this has failed in this manner, I've laboriously gone through and changed all the cell references manually, in every single individual cell. (After doing it once, the arrays usually magically start auto-expanding again.) I'm certain there is a way to make those formulas refer to a named range, instead of all being manual references, but after spending an hour on it, I keep getting #VALUE errors.
I've been using [URL] ..... and pages like it as a reference without success. Obviously, I would prefer that the formulas auto-update without issue per my first issue above, but in the event that they don't, it'd be really nice to just change the ranges in one location and have that propagate across the sheet.
The following is a medical reference equation. I have gender in column B, height in column G and age in column I. There are three age ranges: child, adolescent and adult. These are the individual equations: ...
I'm trying to use multiple arguments for a date, but they are not going through for some reason
the two I'm using are: =IF(month(a1)=month(today())+3,"yes","") =IF(year(a1)=year(today()),"yes","") each one is fine on its own, but if I try to put them together using "and" it does not work.
I am trying to have the word yes show up when we are three months away (not 90 days), but also I want it to show only this years results and not next years.
V = 100 x [E /(IY + YP)] x Sqr. [((R + G)/2 / (IY + YP + F)] V = 100 x [6.10 /(5.60)] x Sqr. [((10.2+2)/2 / (5.60 + 2.1] V = 108.93 x Sqr (.79) V = $96.89 / share as of 7/18/2014
E = Earnings in $ / share 6.1 (b9) IY = Interest rate in % 5.6 (b10) R = Profitability Factor in % (internal growth rate)10.2 (b11) G = Earnings Growth in % / yr. 2 (b12) F = Inflation Rate in % / yr. 2.1 (b13)
Here's what I have, result should be $96.89, I need to tell excel to take square root of everything to the right not just (B11+ B12)
I need some assistance in using an array formula which averages based on multiple arguments. The relevent range names in my input data are:
Col A - Manager Col B - Report Type Col C - Days taken to Complete
and I have used Validation Lists to include several different Managers and 5 Report Types which I'll call A - E, with Days taken to Complete an input. Each Manager may have 0 to numerous instances of each report during each period.
I'm trying to build a table which includes each manager and the average number of days taken to complete each report. The complication is that, while Report A & B are separate reports, for this purpose I need to get an average for A & B together.
For C, D & E, I have used the following: {=AVERAGE(IF((Type="C")*(Manager="Smith"),Days_taken_to_Complete))}
but this doesn't work. Could someone please advise me the correct formula if either of two states in a named range are to be used to average a second named range?
Im trying to build a little database and the closest thing i have come to manage what i want to do is with IF and Vlookup function.
I have 1 "main page" lets call it "sheet1" Then i have nomerous of "secondary pages" we call them "sheet2", "sheet3" etc.
The idea is that on my "main page" im using 3 colums "A","B" and "C". "A" and "B" helps deciding where my VLOOKUP function should find the correct value.
The "A" column is planned to decide in what sheet to look for seach word(which is written in "C" column)
Basicly if "A1" is saying "2" its gonna do my VLOOKUP in "sheet2" , and if "A1" sais "3" its gonna look for my "search word" in "sheet3" etc.
My formula at this point (witch is working for 2 pages) =IF(A1=2;VLOOKUP(B1;sheet1!A1:B6;2;FALSE);IF(A1=3;VLOOKUP(B1;sheet2!A1:B6;2;FALSE)))
This is working perfectly. If i write "2" in "A1" and "car" in "B1" VLOOKUP jumps to "sheet1" lookup "car" and return the value in the second column (in this case 2" and if i write "3" in "A1" and keep "car" in "B1" VLOOKUP jumps to "sheet2" and return the carvalue for this sheet (in this case 22).
Then the problem The problem is ofcourse that if i wanna continue with this formula in the same box, i wanna make it keep looking in more sheets depending on what number i have in "A1" If i put number 5 in it goes to "sheet5" and look for "car" and return valuve.
But at this point the formula is too big for excel.
So i guess my question is. Is there any workaround for this? Can anyone come up how to approach this in another way? (im out of ideas) Or am i doomed and have to learn programming to get my idea to work?
I am have these values based on dates from 1st till 31st and each value is in the alternate cell on the worksheet. For example, value for the 1st is in Cell A1 and the value for 2nd is in cell C1 and so on.
I need to sum all these values for different dates. I am using the sum() and it gives me an error like "too many arguments in the formula".
Do I need to do something else or apply a different formula? Maybe a UDF or a macro?
But I've read that the new versions of excel allow for up to 64 nested if functions. I need to nest about 5 if functions, but after I write out the 3rd excel says there are too many arguments.