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

Reply ↓  Report •

✔ Best Answer
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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

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 is
throwing you off.

There is no exact match for your search, which evaluates out to: HFA0.91

So it finds the next lowest match to the letter H which is F and returns row #4,
within your array, which is 25%

MIKE

http://www.skeptic.com/


Reply ↓  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?


Reply ↓  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 Bond
and again there is not exact match so it select the next lowest match
for 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 TEXT

MIKE

http://www.skeptic.com/


Reply ↓  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?

Reply ↓  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 to
help eliminate errors.

Should work, but not tested extensively.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#6
August 7, 2018 at 19:54:09
✔ Best Answer
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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  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.


Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Ask Question