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
ADVERTISEMENT
Nov 11, 2009
I have this equation
=(INDEX(abc!B8:D18,MATCH(Sheet1!C39,abc!A8:A18),MATCH(Sheet1!D39,percentiles,0)))
it gives me this #N/A. I'd rather have it show $0.00.
View 9 Replies
View Related
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
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
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
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
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
Oct 15, 2009
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.
View 11 Replies
View Related
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).
Edit
HTML removed by RAM and replaced with a new HTML further down.
View 9 Replies
View Related
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
Mar 8, 2007
Why am I getting the "#N/A"s in the enclosed example file.
View 3 Replies
View Related
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
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
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
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
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
=INDEX(A3:A12,MATCH("*"&A19&"*",B3:B12,0))
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.
ARTICLE:
PART NUMBER
Location:
XCA123
PLU546
A1
[Code] ..........
View 2 Replies
View Related
Aug 6, 2008
I have a formula
=INDEX(Wholesales!A:C,MATCH(Data!A2,Wholesales!A:A,),MATCH(C2,Wholesales!B:B,))
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
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
View Related
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.
=INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,1)+1,0)
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
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
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
Feb 28, 2010
I have the following arrangement
PVCu_Woodgrain_DoorF190210300#REF!
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;
=INDEX(INDIRECT(ARRAYDR1),MATCH(DOORSTYLE,INDEX(INDIRECT(ARRAYDR1),0,1)),MATCH(DRSZ1,INDEX(INDIRECT(ARRAYDR1),1,0)))
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
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
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
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
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
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
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
Feb 5, 2013
Is it possible to use an Index/Match formula that looks at multiple criteria?
View 1 Replies
View Related
