# Index Match Or Array Search Function

Jul 30, 2014
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.

View 3 Replies
ADVERTISEMENT
Jan 20, 2010

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:

View 2 Replies
View Related
May 8, 2013

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?

View 1 Replies
View Related
Nov 6, 2013

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"))}

View 5 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
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
Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

View 2 Replies
View Related
Jun 3, 2014

=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?

View 8 Replies
View Related
Jan 5, 2010

I created an Index/Match array forumula in the worksheet. It works.

View 2 Replies
View Related
Jan 26, 2010

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:

{=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?

View 9 Replies
View Related
Dec 2, 2009

sheet 2

column C is list of chemicals (to give context)

column A is a list of the publication numbers they apear in

column B is GOING to be the list of publication titles they are in

sheet 3

column A is a complete list of every publication number in our library

column B is the corresponding publication title

what i would like to do is extract the title from sheet 3 and input it into column B sheet 2 corresponding to whatever pub number is listed in column A of sheet 2

the code i have so far is in column b sheet 2 "=INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0))"

now the problem i have is this works perfectly when only one pub is listed in a cell in column A sheet 2 but when there is more than one pub listed (ie "pub number" next line in cell "another pub number") it gives a N/A result.

is there anyway to call up multiple pub titles from the multiple pub numbers within one cell

View 5 Replies
View Related
Sep 29, 2011

I have been working on this 2 weeks and have finally come to the conclusion. I am trying to use index/match to find an article number in any 1 of 3 attribute files workbooks. I have been am using Vlookup, but the Master attribute file workbooks DO NOT list the article number in column A (but list them in F) and therefore I have been creating an altered copy every month to place the article numbers in column A so VLOOKUP would work. Index/Match would be a more efficient look up formula as I could link directly to the Master Attribute files without altering the workbooks.

Below is the formula I have been using that looks in 2 attribute files workbooks

=IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)),

IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE)),"",

(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE))),

(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)))

Below are the three (the new formula would incorporate a 3rd attributes workbook "Other" to the search criteria) Index/Match formulas I would like to "Tie" together to find the article number in any of the 3 attribute files workbooks and return column A if the article number is present or nothing if the article number is not present in any of the 3.

=INDEX('C:JDAAttributeFiles[OtherAttributes.xls]OtherAttributes'!A:A,MATCH(A8,

'C:JDAAttributeFiles[OtherAttributes.xls]OtherAttributes'!F:F,0))

=INDEX('C:JDAAttributeFiles[PartAttributes.xls]PartAttributes

[Code] .....

View 9 Replies
View Related
Sep 3, 2012

I have a set of values in column A in sheet 1 and a compilation of some of the same set of data in column A in sheet 2. Using the index match function, I have been able to list the corresponding values in column B of sheet 2 onto column B of sheet 1 (for those values which are common to both sheets - matching occurs between column A in both sheets). However, I now have some of the set of data in sheet 2 and the rest in sheet 3 (again, in column A). Is there any way of using index match to search in both sheets and index the corresponding value from column B of the sheet containing the matched value in column B of sheet 1?

I have tried this by nesting the index match functions for each sheet into two separate IF arguments but haven't had any luck. Is there a better way of doing this?

This is what I have currently tried in Cell B2 of sheet 1:

=IF(INDEX(Sheet2!$B$2:$B$3001,MATCH(A2,Sheet2!$A$2:$A$3001,FALSE),1),IF(INDEX(Sheet3!$B$2:$B$3001,MATCH(A2,Sheet3!$A$2:$ A$3001,FALSE),1)))

Excel keeps saying this formula has errors but I have no idea as to what changes I need to make to get the formula to model my situation. Then again, I'm not even sure if this is the approach I should be taking to create a formula that models my situation.

View 1 Replies
View Related
Jun 22, 2014

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!!)

View 6 Replies
View Related
Jun 25, 2014

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.

View 10 Replies
View Related
Aug 2, 2012

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.

View 1 Replies
View Related
Sep 13, 2012

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]

View 8 Replies
View Related
Sep 18, 2012

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?

View 9 Replies
View Related
Mar 10, 2014

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?

View 6 Replies
View Related
Dec 3, 2013

let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.

[Code]....

This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.

I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:

This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in

Match: Lookup value = $E3

Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1

match type = 0

This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered

Match: Lookup value = $A$4

Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452

match type = 0

Index: array = $BA$434:$DN$452

So I think my final function is

[Code] .....

But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.

View 2 Replies
View Related
Dec 2, 2013

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))

View 1 Replies
View Related
Jun 5, 2013

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.

View 1 Replies
View Related
Jun 9, 2014

I am building somewhat of a rudimentary search engine that looks for a name, address or unique identifier, using an Index Match formula that searches with a wild card:

=INDEX(Name,MATCH("*"&F3&"*",Name,0))

"Name": is the defined name for column C on a separate sheet.

F3: is "search field"

The current formula (above), only returns the first match. The source data is only 927 lines so not overly large. I would like to have an additional page that will show the potential results from the wildcard search via a formula. Therefore needing results; 2, 3, 4, 5 etc. from the Index Match. I have searched high and low for solutions but I have found none in regards to retrieving multiple results from a wildcard search, only on a specific term.

View 3 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
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
Oct 1, 2013

I'm trying to create an index array that resizes its array starting location.

{=IFERROR(INDEX($V$17:$AU$17,1,MATCH(TRUE,V18:AU18

View 3 Replies
View Related
Mar 24, 2013

I've been working on a payroll program for my small biz. I'm close to done BUT...When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.

Columns:

Gross Fed FICA Medicare State Net

F G H I J K

Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.

My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?

View 1 Replies
View Related
Jan 2, 2013

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

View 6 Replies
View Related
Jan 19, 2014

Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.

The attached spread sheet has performance data for a group of employees.

What I need to do is find a particular employee then return a value for one of the category's.

For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.

Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.

Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.

Maybe Offset-Index-Match is not even the way to go here?

View 7 Replies
View Related
Dec 28, 2013

Can we use an array like {1;3;4;5} as Row or Column argument in INDEX function? Like shown below

=INDEX($A$1:$E$10,0,{1;3;2;4;5}) OR =INDEX($A$1:$E$5,{1;2;4;5;3},0)

View 9 Replies
View Related