Any Alternative To INDEX And MATCH Formula

Mar 18, 2009

I have the followings in my spreadsheet.

a) Column A: 900 serial nos of Work Orders. Each is unique.

b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.

Note: Column A & B are the main reference.

c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.

I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised.

View 9 Replies


Alternative To Index And Match Array Formula?

Sep 18, 2012

complex formulas in Excel but managed to work out and create the following formula;


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

Index And / Or Match Formula With Multiple Match Required To Return A Value

Jan 21, 2014

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?

View 3 Replies View Related

Adding A Third Match To An Index Match Formula

Oct 6, 2009

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:

View 3 Replies View Related

INDEX As An Alternative For INDIRECT

Feb 18, 2009

In 1 of my spreadsheet I make use of the function INDIRECT to access cells in another spreadsheet. This works fine but has the disadvantage that both file have to be open.

It seems that INDEX can do the same, but: the sourcefile doesn't have to be open. I have tried it and this works.

However: the directory of the file I'm working on will change in the near future (maybe more than once)
Therefore I want 1 central place with the directory and file names and use these in the INDEX function

This is where I get into trouble
I'm not sure if it is possible, but if it is, some advice is needed on how to do this.

View 7 Replies View Related

Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately

Oct 8, 2009

I have this table




As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i

I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on

I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.

View 9 Replies View Related

Index/match Formula

Jun 18, 2009

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?

View 14 Replies View Related

Index Match Formula

Oct 15, 2009

Guys im stuck on the fact of how to pull the correct information through for a named 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.

View 11 Replies View Related

Index(Match) Formula

Nov 22, 2007

Need to place an Index(Match) formula in Sheet 2 B2 (as for example). The lookup values in Sheet 2 to be matched with Sheet 1 will be A2, B1 and J1.

In Sheet 1 (Edit) I'm only interest in the subtotal and Sheet 2 J1 represents the subtotal for the week that ends with J1 date.

The Index should be the whole Sheet 1 (if possible) for further expansion (and I do have way more than what is presented below).


HTML removed by RAM and replaced with a new HTML further down.

View 9 Replies View Related

Index Match Formula

May 17, 2006

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?

The value that should be returned is "416"

View 9 Replies View Related

Index Match Formula #N/A

Mar 8, 2007

Why am I getting the "#N/A"s in the enclosed example file.

View 3 Replies View Related

Formula Index Match Return 1st Value Then 2nd And 3rd?

Aug 4, 2014

How can I modify this formula so it retrieves the 1st matching value (like always), but then the 2nd match, 3rd, etc.:

{=INDEX($A$2:$D$6,MATCH(F2:$F$6&G2:$G$6&H2:$H$6,$A$2:$A$6&$B$2:$B$6&$C$2:$C$6,0),4)} sheet attached: Example.xlsx

View 3 Replies View Related

Index Match Formula Not Working?

Jul 10, 2014

I am not sure where the error is in the formula. Also I would like to add one more condition to it.

Another problem- I created Drop Down using Data Validation. Now I need to make the Drop DOWN ICon visible. It becomes visible only when I click on the cell.

View 6 Replies View Related

Index Match Formula Using Range Name

May 20, 2009

I am trying to tidy up a large workbook, and need some help with use of range names in the Match function.

I have the formula working on the attached sample, but wonder whether it's possible to get the Match function Lookup Array to pick itself from the range name.

View 7 Replies View Related


Jun 22, 2012

My spreadsheet has duplicate lookups, which I need to sum. The INDEX MATCH formula will only bring back the first amount in the list, however I need to return the sum of all the matches. I'm assuming I can include a SUMIF, however not sure how to incorporate.

Current Formula Below:

=INDEX('3.5 IFRS Instr Curr YTD in CHF'!$M$27:$CC$400,MATCH('Detail Reconciliation'!$C12,'3.5 IFRS Instr Curr YTD in CHF'!$A$27:$A$400,0),MATCH('Detail Reconciliation'!M12,'3.5 IFRS Instr Curr YTD in CHF'!$M$26:$CC$26,0))

View 9 Replies View Related

Formula To Return Index Match

Feb 17, 2013

In column B I enter a date when a student attended tutoring.
In column C I enter the name of a student attending the tutoring.

In cell D4, I will be entering a student name

In cells D5:D55 I'd like a formula that transposes the dates (if any) that a student attended tutoring. So if a student has their name appear beside the dates ... 14-Feb, 22-Mar, 24-Mar .... then I'd like those three dates to appear in cells D%, D6 and D7.

How to formulate this ... I'm guessing it's going to involve an index-match formula.

View 1 Replies View Related

Using Wildcard In Index And Match Formula

May 29, 2013

How this could be addressed.

Col. A = Article, Col.B = Part Number, Col.C = Location

I am currently using an index & match formula to create a search function. For example if I want to know what article number PLU547 has then I use


This use of wildcard is fine until it comes to part number searches such as PLU54 (which is not in the list). In this instance the formula returns the first article number from the list that starts with PLU54. However, part number PLU54 is a code in its own right and I fear that using the wildcard approach needs a modification to limit the returns.



[Code] ..........

View 2 Replies View Related

Match/Index Formula :: #REF! Error

Aug 6, 2008

I have a formula


Now this returns the correct value for the first row only, then after that I get the famous #REF! error. Any ideas why it only works for the first row?

View 9 Replies View Related


Feb 11, 2009

I would like to write a formula that I believe will be a SUMPRODUCT formula with a very complicated string of MATCH and INDEX (I think). I hope this can be written in one formula - but if I need a couple to get there, that would be okay too. I am modifying an existing workbook that currently does the calculation, but it need a separate sheet for each payment - up to 12, but it can't be easily modified for more, and I need to do about 120. I can do limited SUMPRODUCT formulas and can do some limited MATCH formulas, but I can't begin to figure out how I would string this formula together. Hopefully what I need to do is clear with the excel shot below. If not, I could post a sheet that does the calculation for one payment. Thank you for working any magic you can on this.

I will try to explain how this formula needs to work. The formula needs to calculate interest on a payment, from one date to another date using a set of fixed interest rates that are established for each quarter. So this is what the formula needs to do. The workbook has a table (see below) that identifies the quarter - dates from and to, the number of days in the quarter (may be needed in the formula, but maybe the formula would calcualte?) The first and last quarter would most likely not be applied for the full number of days, since the payment would be after the start of a quarter, and the end date may not be at the end of the quarter. It needs to determine if the payment falls in a quarter, how many days then are in that quarter, and apply the base dollar amount for the number of days at the daily interest rate. So it would need to look at all dates and do this calculation and add each of the quarters together until it gets to the "Calculate to date" (cell J5). In the sheet below, I would put the formulas for each paymant in cells K8 to K14.

******** ******************** ************************************************************************>Microsoft Excel - Test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD5F5D6F6D7F7D8F8D10F10D11F11D12F12D13F13D15F15D16F16D17F17D18F18D20F20D21F21D22F22D23F23D25F25D26F26D27F27D28F28=BCDEFGHIJK4QUARTER DAYS/QTRRATESDAILY RATE     51/1/20013/31/2001909.0%0.00024658 Calculate interest to: 3/31/2009 64/1/20016/30/2001918.0%0.00021918 PAYMENTDATES INTEREST BASE INTEREST77/1/20019/30/2001927.0%0.00019178     810/1/200112/31/2001927.0%0.00019178 112/2/2001 $            2,424  9      21/11/2002               2,486  101/1/20023/31/2002906.0%0.00016438 32/17/2002             21,011  114/1/20026/30/2002916.0%0.00016438 43/19/2002               3,827  127/1/20029/30/2002926.0%0.00016438 54/22/2002             54,971  1310/1/200212/31/2002926.0%0.00016438 65/25/2002               1,255  14      76/24/2002               4,883  151/1/20033/31/2003905.0%0.00013699     164/1/20036/30/2003915.0%0.00013699     177/1/20039/30/2003925.0%0.00013699     1810/1/200312/31/2003924.0%0.00010959     19          201/1/20043/31/2004914.0%0.00010929     214/1/20046/30/2004915.0%0.00013661     227/1/20049/30/2004924.0%0.00010929     2310/1/200412/31/2004925.0%0.00013661     24          251/1/20053/31/2005905.0%0.00013699     264/1/20056/30/2005916.0%0.00016438     277/1/20059/30/2005926.0%0.00016438     2810/1/200512/31/2005927.0%0.00019178     Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Index - Match Formula With Dates

Mar 18, 2009

I am currently using the following formula and it gives the result P-10, P-10, P-11 below dates that are manually entered. I can not figure out why the date 10/31/09 returns a P-11 value when it matches a P-10 value? All other dates work expcept for the 12 period ending dates. Any help would be greatly appreciated.


10/17/09 10/24/09 10/31/09
P-10 P-10 P-11

01/31/09 P-01
02/28/09 P-02
04/04/09 P-03
05/02/09 P-04
05/30/09 P-05
07/04/09 P-06
08/01/09 P-07
08/29/09 P-08
10/03/09 P-09
10/31/09 P-10
11/28/09 P-11
01/02/10 P-12

View 9 Replies View Related

INDEX / MATCH Formula Variation

Apr 17, 2009

I would like to modify my INDEX-MATCH formula to "look for" my second creteria in a range of cells (ie. the Type 1 to Type 3 range shown below).

I am stuck with the Type data spread across a range of columns.

InputSite: Island(Criteria 1)Type: Direct(Criteria 2)Cost: $ 10 (Desired Result)Data SiteType1Type2Type3CostIslandDirect $10 MainIndirect $20 SecondaryOther $30

View 9 Replies View Related

Array/CSE Formula Using INDEX(MATCH())

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:


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

Index, VLOOKUP, MATCH Formula

Feb 28, 2010

I have the following arrangement

PVCu _woodgrain_Door is a drop down list that selects from a number of named ranges.

The Cell that shows (F1) and is called DOORSTYLE with 6 choices F1, F2 ... F6 (I cannot change these to numeric only values)

300 is the 2 previous cells added together. It is called DRSZ1 and will match a numeric value in the named array.

My formula cell with the issue has this formula;

I know MATCH(DOORSTYLE,INDEX etc. should be a VLOOKUP to find F1 in the named array, but I just cannot get it right.

View 9 Replies View Related

Stop #N/A! In Index Match Formula

Aug 7, 2007

I am working on a formula that, eventually, I would like to be inserted into cells using VBA's FormulaArray Range function. Unfortunately, in its present state, the formula is too long and clumsy to meet the 255 character limit on that function. While I know that I can easily shorten the current formula with simple adjustments, such as using named ranges, I still am forced to ask whether the design of the formula itself could not be improved.

The heart of the formula is an Index/Match combination which will search if any line on the 'Archive' sheet meets the three criteria on the line specified in the 'Data Log' sheet. If so, it returns the value from a particular column of the matching row on the archive sheet. If not, it returns N/A.

=INDEX(Archive!$E$1:$E$1999, MATCH(1, IF(Archive!$B$1:$B$1999='Data Log'!$A2, IF(Archive!$A$1:$A$1999='Data Log'!$C2, IF(Archive!$D$1:$D$1999=1,1))),0))

In an effort to circumvent this unsightly N/A result I have attempted to use the ISNA function paired with an IF statement. The idea is that if the result is N/A then "Pending" will display instead. Otherwise the result of the indexing formula will display. This approach is outlined below:

=IF(ISNA(index function), "pending", index function)

The problem, of course, is that the indexing formula is rather long, and, with this approach, I am required to put its entire contents in the formula twice. I would greatly appreciate any help in streamlining this formula. Remember, the final goal is simply to search the 'Archive' sheet for a single line meeting three criteria and return a value from a fourth column, or if not return "pending".

View 5 Replies View Related

Using INDEX MATCH Formula To Compare Two Fields

May 6, 2014

I am trying to compare two fields using the INDEX MATCH formula. One field needs to match exactly and the second field should return the closest match to a date. I have attached an example where I am trying to find the closest production date (column F) for the matching ID that is before the complaint date.

View 8 Replies View Related

Index Match Formula For Compiling Data

Jul 7, 2014

I am working on a spreadsheet where I need to have data from different sheets added but based on the instructor whose data I want to see in the summary sheet. I want to be able to type the name of the instructor in a cell and have all of the data appear below. I believe that an index match formula would work, but I do not know how to have the data from the different sheets added nested in the formula.

View 2 Replies View Related

Index Match Formula For Multiple Criteria?

Dec 3, 2013

In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.

In cell B12 i have an index match that I can not get to return a value.

Is Index Match the correct method of returning a result? Or have I just got the formula wrong?

View 1 Replies View Related

Index-Match Formula Tied To Cell Value

Dec 28, 2009

I am trying to get an Index-Match formula to search a different sheet depending on the value selected in a particular cell using a dropdown list. See the attached workbook.

View 3 Replies View Related

Index/Lookup Formula (dont Match Put 0 Instead Of #N/A)

Nov 13, 2008

=INDEX($AS$9:$AT$33, MATCH(B4,$AS$9:$AS$33,), MATCH("PERCENTAGE",$AS$9:$AT$9,))

That's all fine but some of the items in the list will not be able to match the lookup table and in this case I want it to say 0 (zero), but instead its saying #N/A which I understand means data unavailable. Is there a way of saying if so and so doesn't match put 0 instead of #N/A?

View 4 Replies View Related

Index Match Formula With Multiple Criteria

Feb 5, 2013

Is it possible to use an Index/Match formula that looks at multiple criteria?

View 1 Replies View Related

Copyrights 2005-15, All rights reserved