# 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/

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

#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/

Related Solutions

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

#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/

#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

#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.

 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/