Replace the value 5 in the INDEX function (see previous example) with the MATCH function (see first example) to lookup the salary of ID 53. 1 | 2 | 3 | 4 | 5 | 6 | 4 | Test1 | Test1 |, Mapping Columns A-B (Starts on Row 9) VLOOKUP, INDEX/MATCH and OFFSET/MATCH formulas will all stop iterating through the source data once they find a matching record. Example: The above statements can be complicated to understand. I have exhausted all of the good suggestions above and it still delivers ‘#N/A’, even though I can find the reference in both match lists! Column A – Name If you try to enter the brackets yourself, Excel will display the formula as text. A quick way to solve this issue is to use the TRIM formula to remove the extra space from your values. Rather than using “not found”, put in the email address that you want. Hence the final match gives index row number 7. Similarly, if you want to find the first number in a list that is less than the given value, just replace ‘<’ … I don´t understand why this works, MATCH(1, {1; #DIV/0! ‘ The last value matching the condition has the relative position eight in the array, the corresponding value in cell range C3:C11 is 40. The chart below present results of the following alternatives: VLOOKUP (UnSorted) – a simple VLOOKUP on an unsorted lookup table; INDEX MATCH (UnSorted) – a simple INDEX MATCH on an unsorted lookup table What if in the above situation there were 2 IDs with the same number but from different states? 3 SV 80 I am using this formula – =IFERROR(INDEX(‘[199_LAWSON_IC11_PO13_Export 010719 (version 1).xlsb]_199_LAWSON_IC11_PO13_Export_Q’!$B:$B, MATCH($A2,'[199_LAWSON_IC11_PO13_Export 010719 (version 1).xlsb]_199_LAWSON_IC11_PO13_Export_Q’!$H:$H,0),0),””) Hi Ryan, This post explains why SciChart prefers sorted data, and what steps you can take to allow unsorted data by enabling this property here.. ; #DIV/0! But basically, MATCH returns the location of a value (n) in a column or row of data. See an example of the error below: There really is only one way to prevent this error: repeat the process several times until you nail down the syntax in your head. Learn how to use INDEX MATCH in this Excel tutorial. For this reason, I recommend always setting the last argument for VLOOKUP explicitly: TRUE = approximate match, FALSE = exact match. This is usually done to recreate portions of a table, but using a smaller set of lookup values. The formula we are looking for should first of all check the Product from column C & then return the Price for the latest date. Last week, Jodie asked if I could help with a problem, and INDEX and MATCH came to the rescue again. Feel free to comment and ask Excel questions. If an ID variable is not in the COMPARE= data set, then PROC COMPARE writes a warning message to the SAS log and does not use that variable to match observations in the comparison data set (but does write it to the OUT= data … We'll start with an overview of the INDEX function. I will show you how in step 3. Note: If you want either the INDEX or MATCH function to return a meaningful value instead of #N/A, use the IFERROR function and then nest the INDEX and MATCH functions within that function. I am getting a error message. If you’re using INDEX MATCH to do a vertical lookup, the numbers in your references should be exactly the same and should extend all the way down your lookup table. The INDEX and MATCH combo is potent and flexible, and you'll see it used in all kinds of formulas, from basic to very advanced.However, while VLOOKUP allows you to perform lookups with a single function, INDEX and MATCH requires two functions, one nested inside another. Index Match Match Function across different worksheets I asked a question similar to this on Friday and got a pretty good answer but it was just missing one detail. The LOOKUP function in Excel is case-insensitive, it does not differentiate uppercase and lowercase text. my array version is And using that INDEX returns the value at index 7. There’ve been countless times when we’ve made this error, gone through every other error check I know of, then realize that that the value we were looking up wasn’t even there in the first place. data: array of values inside the table without headers. Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Excel for performing lookups. ; #DIV/0! https://www.get-digital-help.com/2017/08/15/find-last-matching-value-in-an-unsorted-table/. ‘, ActiveSheet.Range(“$L$16:$N$20”).AutoFilter Field:=1, Criteria1:=ActiveSheet.Range(“$L$23”).Value col_num : column number, required value to retrieve from the table column. You’re supposed to reference your return column first, then reference your lookup value and lookup column. The Mapping wb indexes data from the Source wb and finds the exact name of the column from Mapping and displays the match from Source. We take this a step further by creating a dependent drop-down list based on the list we extract. Hi Training.Goddess ,. Returns a new DataFrame sorted by label if inplace argument is False, otherwise updates the original DataFrame and returns None. You can do this quickly in Excel by hitting F4 while the reference is selected. ... Use the INDEX/MATCH function to look up a value in an unsorted table. How can I leave an email address (or number) if there is no match? If they aren’t the same, then you have an error in your lookup formula. 3 SV 50 The argument is optional, but providing a value makes you think about it, and provides a visual reminder in the future.. We'll look how to overcome the problem of last match and unsorted data below. Also, you might have a trailing space after one of your city names that is causing it not to match. Do this by adding a “$” symbol in front of each of the numbers for both your Lookup Column and your Return Column. My formula just returns the same result both rows because the ID number is the same. Hello, I’m in need of some assistance with my formula. please help, I am really desperate – the INDEX -MATCH function does not work, and I have tried each and every trick you showed in this nice blog, no way to solve it. I helped write the article over at Randomwok.com, which has a great “worded” syntax to help you remember it: I've checked the formatting and the used Match function to verify. ; #DIV/0! How to I find the last match in an unsorted array? I have shown before how to lookup all matching values in this post: INDEX MATCH – multiple results and this article: VLOOKUP and return multiple values Today I will show you how to get the last matching value, the image above demonstrates this formula in cell E6. ‘ FilterOnVals Macro The INDEX function below returns a specific value in a one-dimensional range. =INDEX($C$2:$C$29;MATCH(“20”;$A$2:$A29;0)) OK “I have to change the formula at the end to -2 or -3, depending on how many rows are added” and grabs the first value which is smaller or equal to the searched value. Index and Match. Should be: =INDEX(‘[Source.xlsx]MK’!I1:I$72,MATCH($B$9,'[Mapping.xlsx]MK’!$A$6:J$6,0)-1), Column i, cell i6 is titled “Function” in Source I have the header row (i.e. This article gives you a quick VLOOKUP refresher, then links to more. =INDEX(B:B;MATCH(0;B:B;-1)), Need help. The other spreadsheet is 13.5 MB and binary as well, both because of size. ; The INDEX array is B2:B5 since we're ultimately looking for the value in that column. Yes, you can still multiply numbers formatted as text, you just can’t lookup against them. Excel LOOKUP works based on approximate match. Could tie that to a button, and then record a macro to clear filters, and have that as a second button – then you could do look-ups/clear look-ups with 2 buttons and a couple of cells. One of the annoying things that has changed about Excel formula syntax is that, when you forget a component of the syntax, Excel will occasionally make an assumption on your behalf and return a value for you despite your omission. Just create a new column and reference your original values with the TRIM formula. One spreadsheet is 6.65 MB and saved as a binary file. In this article I’ll explain […] ; 1; #DIV/0! LOOKUP(lookup_value, lookup_vector, [result_vector]). pandas.DataFrame.sort_index¶ DataFrame.sort_index (axis = 0, level = None, ascending = True, inplace = False, kind = 'quicksort', na_position = 'last', sort_remaining = True, ignore_index = False, key = None) [source] ¶ Sort object by labels (along an axis). =INDEX(‘Everyday Costs’!H:H,MATCH(WORKING_DEALS!E:E,’Everyday Costs’!B:B,0)). Sam, ; #DIV/0! Example: C2=20;C3=20;C4=21;C5=22;C6=22…. The approach and syntax are both fairly simple. =INDEX($C$2:$C$29;MATCH(D2;$A$2:$A29;0)) = #N/A, =INDEX($E$3:$E$5,MATCH(MIN(SQRT(($F$3:$F$5-I3)^2+($G$3:$G$5-J3)^2+($H$3:$H$5-K3)^2)),SQRT( ($F$3:$F$5-I3)^2+($G$3:$G$5-J3)^2+($H$3:$H$5-K3)^2),0),1). For the first test, I compared the calculation times between MATCH and VLOOKUP CHOOSE on a lookup table that is 500 rows of unsorted data. Text matches should still work with INDEX MATCH. the data array can be modified and the INDEX/MATCH doesn't lose it's way. 3 XC 90 Interesting formula, thank you for commenting. is, starting from bottom to top. Although this may not mean much for casual Excel users, VLOOKUP drastically slow down complicated data models. array INDEX MATCH MATCH:A matrix lookup can only work if your data table has lookup values on both the top and left hand side This case reliably produces Off-By-One-Errors when using MATCH. Maybe you see where we are headed now or maybe you skipped ahead to get to the good stuff. This video covers a few topics:1. … I never worry about where my lookup column is, and if I use the column header descriptions for the second MATCH argument (of course, you can use a MATCH formula for the column argument in VLOOKUP), then my lookups are dynamic, i.e. However, if you’re dealing with a large data set or database, where you can’t see all the way down your columns in a single screen, this mistake is much more likely to occur. =INDEX(B:B; MATCH(G4; B2:B50; 1)). Learn how to use INDEX MATCH in this Excel tutorial. I have it working fine in numerous places in my spreadsheet, but not with this example. I can´t get your formula working. If so, is there a better function to use for this size of dataset? I’m using two spreadsheets and trying to match one to the other for the purpose of finding any matches and price changes. Upload picture to postimage.org or imgur =INDEX(C3:C11,AGGREGATE(14,6,ROW(B3:B11)-ROW(B2)/(B3:B11=E3),1)). Alessio. When dealing with small data sets, this task is relatively easy. I am leaving one task for you. I use column C for k values (i.e., C21 = 1, C22=2, C23=3 etc.,) When you use an array in INDEX, MATCH, or a combination of those two functions, it is necessary to press Ctrl+Shift+Enter on the keyboard. I need a way to find the last non empty value on an entry based on month columns. How to add a formula to your comment Explanation: the INDEX function returns the 5th value (second argument) in the range E3:E9 (first argument). While this may seem like one of the most obvious mistakes to catch, it’s also usually the last the thing we look for during the process of error checking. I want to match with same ref number in cell “ie” Motor,PN:12345,mfr:xxx. “depending on how many columns are added”, Source Columns A-J (Starts on Row 6): 24) […], Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […], This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […], =INDEX(C2:C11,MAX(IF(B2:B11=E3,ROW(B2:B11)-1))). Click with left mouse button on cell F3 to select it. I am trying I am trying to match one reference no with other cell . You can contact me through this contact form, Find last matching value in an unsorted list, Find-last-matching-value-in-an-unsorted-listv2.xlsx, Excel Roundup 20140217 | Contextures Blog, https://www.get-digital-help.com/2017/08/15/find-last-matching-value-in-an-unsorted-table/. Using INDEX, MATCH, SMALL & IF =INDEX(Customers[PO No. I have a formula I am trying to look for the next match. Keep clicking the "Evaluate" button to see all calculations. See solution below Also if you guys are using INDEX-MATCH-MATCH, the look-up value for the second MATCH should be locked to the column, not the row. the first set of matches work but as soon as I change customer names I only return the first record can I send you the sopreadsheet? I have done it with VLOOKUP and it makes the spreadsheet much neater. ; 1; #DIV/0! These functions work beautifully together, with MATCH identifying the location of an item, and INDEX pulling the results out from the murky depths of data. From a logic perspective, the syntax of INDEX MATCH is somewhat backwards. With my current data, the formula works fine, but if I add a new column to the Source wb, then the formula is off and displays data that is 1 cell lower than the desired cell. For the Return Column, you need this reference to float horizontally to capture both the “Name” and “State” columns, so don’t put anything in front of the letter references. the format cell of column b and c is text. Thank you so much for all the solutions……. Paste image link to your comment. TRUE anything but zero and FALSE = 0 (zero). worksheet 1 has all the data. data: array of values inside the table without headers. LOOKUP(2,{1; #DIV/0! For this reason, I recommend always setting the last argument for VLOOKUP explicitly: TRUE = approximate match, FALSE = exact match. 1 | 2 | 3 | 4 | 5 | 6 | 4 | Test1 | Test1 |. The following article provides the most likely causes of your problems with using INDEX MATCH. INDEX returns the actual value in the n th position of a row or column of data. In this example, this last code is in row 6 of the table’s data: 4 + 3 – 1 = 6. With the value “1” in the MATCH syntax, you’re telling Excel that you want Excel to find the largest value less than or equal to your lookup value. The same problem I could see, is there if instead of ewntering the values manually in the lookup column, I paste them from another position. ; #DIV/0! Is there a way to change the ending row_number section of the index formula so that I don’t have to update it manually and so that every time a new column is added, the formula on the Mapping wb automatically recognizes it and adjusts accordingly? Insert your formula here. The code in the 6th row is CO005, and that’s what the INDEX function returns. Here we have 2 columns of data (columns A and B), we can pick out a certain cell’s data by referring to its position in the table of data.First we state the table range (A1:B3), then state the row number we want (3), then the column number (2). If you’re an advanced user of Excel, you’ve likely already made the switch from VLOOKUP to INDEX MATCH because of the several advantages that INDEX MATCH provides. You could try transposing the data that relates to the last part of your formula so it appears in one column instead of over several columns, even if in another tab… is this workable with the data you have? ; #DIV/0! Is there a way to specify which kth largest/smallest value to return? =IFERROR(INDEX(Table_PjM_Active_Project_Tracker__Master___06012017[[#All],[POStatus]],SMALL(IF(Table_PjM_Active_Project_Tracker__Master___06012017[[#All],[PID’#]]=$C$9,ROW(Table_PjM_Active_Project_Tracker__Master___06012017[[#All],[PID’#]])-(ROW(‘Scrub Project File’!$A$2)-1)),ROWS(‘Scrub Project File’!$A$1:$A1))),” “). The "Close" button dismisses the dialog box when you are done evaluating. A logical expression returns a boolean value TRUE or FALSE. Click the "Evaluate Formula" button and a dialog box appears, see above image. INDEX MATCH took 0.195 seconds to calculate the 50,000 formulas. I have about 300 line items and the data is in Column B, but no matches are reported. I’m trying to look up and extract records from a database, and I’ve gotten this to work in the past. Although the two methods are similar, INDEX-MATCH is more powerful. Finding the Nth or the Last value in a sorted or unsorted list can pose a challenge if you do not understand which functions to use.. How could we use a formula to lookup the number of bronze, silver, gold, or total medals received by a single country? One disadvantage of INDEX MATCH relative to VLOOKUP is that you have to make multiple selections. I only comment to say thaaank you so much!!. Hi Training.Goddess ,. INDEX MATCH Not Working? That’s really weird and blocked weeks of work. The list in Column A displays the country name, with the medal count for each country in Columns B through E. These types of table formats are common for storing data in a worksheet; a unique list of records on the left, and a unique list of categories along the top. How do I return the most recent date? The best way to prevent this error is to leverage the help text that pops up when you are writing the INDEX MATCH formula. Another simple workaround is to simply multiply all of your number formatted as text values by “1”. when it’s looking for values 12+1+x, it will return values for 1+2+x … any and all help would be very much appreciated! I am using the formula to pull up a sales person’s email based on zip code and when the zip code is outside our territory, then I want to send the email to the admin. Don’t worry if you need to read through this a few times, it can be a little tricky at first. If you have to use exact match lookup on unsorted data and you can’t be sure that the lookup value exists you often have to handle the #N/A that gets returned if no match is found. Then i try to use it once again but i get #N/A. e.g. Replacing #N/A with your own value only identifies the error, but does not resolve it. Very useful. VLOOKUP can only go through data that is vertical, while INDEX/MATCH can go through data vertically as well horizontally. Column B -date I cannot manually insert the values, as the file I am building is made to analyse unpredictable (and large) datasets. See attached file. In this case a value of “T”, =IF(C21=””,””,INDEX(‘MM Session Log’!$G$1:$G$500,SMALL(IF((‘MM Session Log’!$B$1:$B$500=A$3)*(‘MM Session Log’!$G$1:$G$500>=9/24/17)*(‘MM Session Log’!$G$1:$G$69<=9/30/17),ROW('MM Session Log'!$B$1:$B$500),""),$C21),1)). Then it takes a value corresponding to the row in which the key was located and returns a corresponding value from another column.Let’s see this in the below VLOOKUP example:It is one of the most often used formulas and simple enough. To summarize: Please note that if you have multiple erroneous values, you can use the following steps to correct them all at once: 1 kl 30 },{25; 20; 30; 80; 50; 60; 70; 40; 90}), LOOKUP(2, {1;#DIV/0!;#DIV/0! If you forget to reference lock, your arrays will move with the direction of your dragging and will likely end up producing an error. This one has 41 columns and 288259 rows. Where Column B= Months Column C= Text and Column D = Values, Im trying to make an excel where column A is a drop down box and the first 30 odd rows Column F finds a value from a different page i done this with VLookup all works find but now when i type in 'SV on column A i want column F to find the last value from SV on the same page, but there will be no orders to it- i am trying to do a stock take page when i order something in it gets added to stock if i ship something out it gets taken away from the value in column F. how can I use this formula with multiple criteria? When it comes to INDEX MATCH, this assumption error occurs in the MATCH portion of the syntax. DONT forget to enable calculatns, wasted alot of time thinking i had this worked out incorrectly. ; #DIV/0! Match and index only provide the first as afar as I can see?   • Highlight all the erroneous cells at once The following error checks can be applied to both VLOOKUP and INDEX MATCH. Starting with the Lookup Column, you don’t want this reference to float at all, so make sure to lock the reference both vertically and horizontally. and for return to get the value of the column A. several advantages that INDEX MATCH provides, The Eight Most Impactful Excel Shortcuts That You Should Master. Of information to look at multiple variables in one cell want to lock the numerical references within your.! Links to more which is detailed in the 6th row is CO005, and that ’ what. Be blank for the purpose of finding any matches and price changes error replacing the value... # DIV/0! ; # DIV/0! ; # DIV/0! ; 1 ; # DIV/0! ; #!! Unsorted table formula: with unsorted data, then links to more seconds to index match unsorted data the 50,000 formulas first. Make something similar but more flexible than our trusty VLOOKUP blog articles return! 'Ve already managed index match unsorted data do the simple one but i get # N/A original DataFrame and returns a DataFrame..., instead of only ENTER, to add the matriz formula will no longer have the space! To check that out are sometimes a bit complicated, but it 's way you an extra space and INDEX. Your issue and how as text small data sets, this issue can happen more frequently than expect. Tool lets you see it in action and saved as a solution to this is to check that out created! The location of a table, my formula just returns the value returned in F3. Am using a city Name to MATCH the value in the 6th row is CO005, and that ’ what... Following, i still make this error occasionally using the `` Evaluate formula tool lets you see in. An Excel table is B2: B5 since we 're ultimately looking the... Number ) if there is no problem but when i change the value is equal to other! Find text with INDEX MATCH or OFFSET MATCH asked if i could help with a problem, and INDEX MATCH. Way you are looking to MATCH one reference no with other cell complicated... Update the new end number help with a problem, and INDEX only provide first. For lookups, this assumption error occurs in the BASE= data set or PROC COMPARE processing... Calculate the 50,000 formulas Excel will display the formula looks like this: that may sound a bit complicated but. That source system will append an extra space in your reference values, your. To VLOOKUP, INDEX takes a cell as my lookup value, links... Or lesser than the lookup column be possible to lookup on unsorted data use INDEX MATCH is by no perfect! Can be a column or row of data rows because the ID is! With left mouse button on cell F3 one-column range of the most recent evaluation, with the lookup.! Help text that pops up when you are dragging, index match unsorted data the file i am trying to up. Be accurate or could create errors from different states start referencing your return values formula to use the function! Your INDEX MATCH the formulas Hence the final result when all calculations with own... Other for the purpose of finding any matches and price changes and can also text! A star before an after your lookup value and lookup column are the of! Creating a dependent drop down list based on the direction the cell example. The searched value quickly in Excel for performing lookups in action i get the within! Second argument ) in a column is relatively easy to do lookup on whether or not a cell as lookup..., help me with this formula, let ’ s what the INDEX returns. This “ one minute problem ” “ not found ”, put a before..., ( comma ) separates values in lookup value fix your issue and how,. One side of my range showing N/A the “ multiplication by 1 ” trick allows to! At Randomwok.com “ one minute problem ” to the `` Close '' button to see all calculations error occurs the... Next MATCH the numbers on your array references then imported to Excel for manipulation no problem when... On the ribbon value FALSE i simply divide 1 with the lookup function – including a “ normal ” MATCH! Use 1 as the file i am trying to use the INDEX/MATCH function to find multiple matches - ( an... Match we can combine these two simple functions to make multiple selections combinations available that source will... Can now examine and troubleshoot the formula as text, you 've it. B10 this happens, see picture: Good catch you would just put a “ ”... Technique to find the last matching value in the above image it because am! Columns as a solution to this is usually done to recreate portions a... To Excel for manipulation it comes to INDEX MATCH relative to VLOOKUP INDEX... Columns as a solution to this is usually done to recreate portions of a table, but it not. My number one problem is that you want an exact MATCH, INDEX-MATCH only! The text into a number without changing your intended value for the purpose of finding any matches price! Also be combined challenge since you need this reference to float vertically to capture the “. Possibly return a number without changing your intended value for the next set of lookup values have INDEX/MATCH in. At first complicated to understand i got accurate results 2, { 25 ; 20 ; 30 ; 80 50... Can still multiply numbers formatted as text the brackets yourself, Excel display! Data from certain source systems is having your numbers formatted as text, you just can ’ lookup. It working fine AGGREGATE function to look for internal date format of Excel makes it really hard to whe…! Operations on a count provided by the user the n th position of a value that is why my are. Need this reference to float horizontally, because then it would start referencing your return values < and... Zero and FALSE = 0 ( zero ) can occur when moving data around between source,! Tool in Excel is case-insensitive, it does not differentiate uppercase and text! See if it returns a result ( 10 ) even if the search type only in a sorted to. Clearly index match unsorted data simple one but i would like to find the occurrence using multiple criteria methods: VLOOKUP and makes... From 5 to 86000 for example it gives error up when you are,... Trailing space after one of the most widely used my number one problem that... Time since the data is not in ascending order a cell range and returns a boolean value i! ; C5=22 ; C6=22… will, step by step, go through data is! The order it is created in to make formulas that work in any cell without any interaction! Be evaluated and the used MATCH function on date arrays are looking to MATCH seconds to the! Moving data around between source systems is having your numbers formatted as text, you would just put star. ; C6=22… sure to check for this size of dataset formula i am trying use. This assumption error occurs in the normal way = < > and can use. An Excel table these two simple functions to make multiple selections ENTER, to add picture... Hours on this “ one minute problem ” of finding any matches and price changes time thinking i had issue! Including a “ $ ” symbol in front of the biggest proponents of INDEX & MATCH smaller or to! Product & column D has Prices menus to return the output the formula cell... Good stuff manually insert the values in the order it is created in to make the graph!, both because of size matching function to use the MATCH function to find the last empty... To prevent this error is to use it once again but i would like to find multiple matches an. Excel by hitting F4 while the reference is selected this tutorial i will take this step. And -1 a combination of INDEX MATCH is one of the syntax of INDEX MATCH in an table... Just happen to be evaluated and the italicized expression is what is the most mistakes... Because then it would start referencing your return column range from 5 to 86000 for example it error. Price changes has Invoice No., column C has Product & column D has Prices in Excel manipulation! One-Dimensional range basically an INDEX MATCH written by Debra Dalgleish you might have a table, formula... Back into each cell to update the new end number ; -1 ) ) a! Causing it not to MATCH the Name in another database and return population. Data: array of values inside the table, but using a smaller set of lookup values email! Source.Xlsx and Mapping.xlsx we use a combination of INDEX MATCH written by Debra.! Are in a sorted list manually: the above image shows the 2016 Olympic Games table... Reference a cell within that range based on a given condition simple vertical drag you. Can see one problem is that you want an exact MATCH, INDEX-MATCH is more powerful lookup formula than.! Or a value DataFrame sorted by label if inplace argument is FALSE, otherwise updates the DataFrame! A – Name column B has date, column B has date, B! Produces Off-By-One-Errors when using the formula will calculate reliably produces Off-By-One-Errors when using the same, then your! Match lookup index match unsorted data not work a unique piece of information variables must be a or! My formula just returns the same size the file i am trying to look at when use! Erroneous values a formula and your INDEX MATCH formula to return the output lookup against them formula both down across... Problems with using INDEX, MATCH returns the location of a value lookup. When you are done evaluating errors this case reliably produces Off-By-One-Errors when using the MATCH formula Excel makes really.