# Solved Index Match with Array Not Working Properly

August 7, 2018 at 15:39:32
Specs: Windows 7
 I have an index/match formula as an array that is returning an incorrect result. I know it has something to do with the exact match portion or the order in which my table is constructed but can't seem to get it to work properly.My formula is =INDEX(BN84:BN94,MATCH(BM95&BM96,BM84:BM93&BL84:BL93,1))I am doing Ctrl-Shift-Enter when completing formula.The user selects Loan Program in BM95 and LTV in BM96 and the array index/match formula in BM97 should return the correct coverage amount. In the example below it should return .16 but instead it is returning .25. The reason it should return .16 is because the HFA Bond Program combined with an LTV of 91% which is less than 95% but greater than 90% should be reading row 88 so the result should be 16% (cell BN88).```BK BL BM BN 84 85% HFA Bond 6% 85 85% Fannie/Freddie 12% 86 90% HFA Bond 12% 87 90% Fannie/Freddie 25% 88 95% HFA Bond 16% 89 95% HOMEREADY/Home Possible 25% 90 95% Fannie/Freddie 30% 91 97% HFA Bond 18% 92 97% HOMEREADY/Home Possible 25% 93 97% Fannie/Freddie 35% 94 95 Loan Program HFA Bond 96 LTV 91% 97 Coverage 0.25 ```message edited by mecerrato

See More: Index Match with Array Not Working Properly

August 7, 2018 at 19:54:09
 Based solely on the data in your example table, this array formula seems to work.{=INDEX(BN84:BN94,MATCH(BM95&IF(BM96>95%,97%,CEILING(BM96,0.05)),BM84:BM93&BL84:BL93,0))}What it does is "force" a match to a 5% increment (85%,90%,95%) except where BM97 is above 95%, then it's "forced" to 97%. That way an match_type argument of 0 can be used because there will always be an exact match of the concatenations.message edited by DerbyDad03

#1
August 7, 2018 at 17:06:42
 I think what is happening is your Match() function is returning the correct number, it's in how you have your Text strings concatenated with the percentages that isthrowing you off.There is no exact match for your search, which evaluates out to: HFA0.91So it finds the next lowest match to the letter H which is F and returns row #4,within your array, which is 25%MIKEhttp://www.skeptic.com/

Report •

#2
August 7, 2018 at 17:25:47
 Ok so maybe I should make everything decimals or everything percentages?Whatâ€™s odd is that I highlight the different data points and hit F9 to see the result and it does return HFA0.91 and the array does contain that value.Any suggestions?

Report •

#3
August 7, 2018 at 17:27:49
 Also, just reversing the formula as:=INDEX(BN84:BN94,MATCH(BM96&BM95,BL84:BL93&BM84:BM93,1))Also does not work as the search string evaluates out to 0.91HFA Bondand again there is not exact match so it select the next lowest matchfor 0.9 which is 0.8 which is line #2 in the array.Also, be aware that when you concatenate the two strings they both become TEXTMIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
August 7, 2018 at 17:40:21
 Any suggestions on how to accomplish my goal? Should I use some other functionality?

Report •

#5
August 7, 2018 at 18:44:38
 A small table should work, something like:``` 1) A B C D E 2) 0.97 0.95 0.90 0.85 3) HFA Bond 18 16 12 6 4) Fannie/Freddie 30 30 25 12 5) HOMEREADY/Home 25 25 6) 7) 8) Loan Prgm HFA Bond 9) LTV 0.91 10) Coverage 16 ```The formula then is simplified to:=INDEX(B2:E4,MATCH(B7,A2:A4,0),MATCH(B8,B1:E1,-1))Also, you might want to use a Drop Down list for cell B8 tohelp eliminate errors.Should work, but not tested extensively.MIKEhttp://www.skeptic.com/

Report •

#6
August 7, 2018 at 19:54:09
 Based solely on the data in your example table, this array formula seems to work.{=INDEX(BN84:BN94,MATCH(BM95&IF(BM96>95%,97%,CEILING(BM96,0.05)),BM84:BM93&BL84:BL93,0))}What it does is "force" a match to a 5% increment (85%,90%,95%) except where BM97 is above 95%, then it's "forced" to 97%. That way an match_type argument of 0 can be used because there will always be an exact match of the concatenations.message edited by DerbyDad03

Report •

#7
August 8, 2018 at 04:16:16
 mcconaghy thank you very much for the help but by the time I got back to my computer I saw the solution by DerbyDad03 and it worked like a charm without having to rearrange my table.DerbyDad03 just for clarity, the issue was always the 5% increment that was not giving me the result? I thought it had something to do with the whole issue of using concatenating and that it converts the data to text and therefore you cannot perform any math functions.

Report •

#8
August 8, 2018 at 04:59:47
 I guess you could call it a combination of both math and text issues.Mike is 99%* right in saying that the concatenation changes the output to text which then forces the MATCH function to try and find an approximate match of text, which is always an issue. That is the main cause of your problem. In the case of my suggestion, the formula does the math first (via the IF(CEILING...) clause and then does the concatenation. Once the math is complete, the resulting string from the concatenation will be exact match for something in the concatenated array of the data in the table. The MATCH function doesn't have to deal with/be confused by text strings aren't exact. Does that make sense?*The reason I say that Mike is 99% right and not 100% right is really just a nit. He said:"be aware that when you concatenate the two strings they both become TEXT."In my way of thinking, that should say: "be aware that when you concatenate the two items the output becomes TEXT."In your case, you are concatenating a number and a Text string. The concatenation does not turn each of them into a string, it creates a single string as the output. Now, in defense of Mike's solution (the different table) that has 2 major advantages:1: It doesn't require an Array formula. 2: You can change the values in the table without having to change the formula. I'm not a fan of hardcoding values in a formula if there is a different way of getting the same result. For example, if your 97% becomes 98% because of changes in the rules/law/program, or the 85% becomes 86%, my formula will crash and burn. With Mike's method, all you have to do is change the table. Keep in mind that it might not be you that makes the alteration. Will the next person know how to "force" my formula to work with the new data?In my opinion, Mike's method might be the better long term solution.message edited by DerbyDad03

Report •

#9
August 8, 2018 at 05:52:43
 Just noticed there is a minor error in the table, as posted, I included an extra row number.The corrected table is:``` A B C D E 1) 0.97 0.95 0.90 0.85 2) HFA Bond 18 16 12 6 3) Fannie/Freddie 30 30 25 12 4) HOMEREADY/Home 25 25 5) 6) 7) Loan Prgm HFA Bond 8) LTV 0.91 9) Coverage 16 ```And cell B7 is where the drop down list should go.Sorry, was in a bit of a rush last night.MIKEhttp://www.skeptic.com/

Report •