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

✔ 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

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

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?

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

Any suggestions on how to accomplish my goal? Should I use some other functionality?

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 16The 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

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

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.

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 forsomethingin 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

itemstheoutputbecomes 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

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 16And cell B7 is where the drop down list should go.

Sorry, was in a bit of a rush last night.

MIKE

Ask Your Question

Weekly Poll