Solved Index/Match Array not returning some combinations correctly

August 18, 2018 at 12:08:10
Specs: Windows 7
I am using an index/match formula as an array (provided by an awesome person on this forum) to return a specific result using 3 criteria, I just realized that a particular combination is not returning properly. The formula should return the cell that these three criteria intersect:
BM96 (LTV) LTV Data Range is AZ60:AZ69
BM97 (Coverage) Coverage Data Range is BA60:BA69
BM98 (Credit Score) Credit Score Range is BB59:BI59
Data Table to return value from is BB60:BI69

Here is the formula:

=IFERROR(INDEX(BB60:BI69,MATCH(IF(BM96>95%,97%,BM96)&BM97,AZ60:AZ69&BA60:BA69,-1),MATCH(IF(BM98>760,760,BM98),BB59:BI59,-1)),INDEX(BB60:BI69,MATCH(IF(BM96>95%,97%,BM96)&BM97,AZ60:AZ69&BA60:BA69,-1),MATCH(IF(BM98>760,760,BM98),BB59:BI59,-1)))*100
(I am pressing Ctrl+Sht+Enter) when completing formula

When using the following combination of criteria, the result should be .96 but instead it returns .72 which is the correct column but 2 rows lower than it should be
LTV 92%
Coverage 30%
Credit Score 680

Here is the data:

        AZ      BA	BB  	BC	BD	BE	BF	BG	BH	BI
 59	LTV Coverage    760     759	739	719	699	679	659	639
 60 	97%	35%	0.58%	0.70%	0.87%	0.99%	1.21%	1.54%	1.65%	1.86%
 61 	97%	25%	0.46%	0.58%	0.70%	0.79%	0.98%	1.23%	1.31%	1.50%
 62 	97%	18%	0.39%	0.51%	0.61%	0.68%	0.85%	1.05%	1.17%	1.27%
 63 	95%	30%	0.38%	0.53%	0.66%	0.78%	0.96%	1.28%	1.33%	1.42%
 64 	95%	25%	0.34%	0.48%	0.59%	0.68%	0.87%	1.11%	1.19%	1.25%
 65 	95%	16%	0.30%	0.40%	0.48%	0.58%	0.72%	0.95%	1.04%	1.13%
 66 	90%	25%	0.28%	0.38%	0.46%	0.55%	0.65%	0.90%	0.91%	0.94%
 67 	90%	12%	0.22%	0.27%	0.32%	0.39%	0.46%	0.62%	0.65%	0.73%
 68 	85%	12%	0.19%	0.20%	0.23%	0.25%	0.28%	0.38%	0.40%	0.44%
 69 	85%	6%	0.17%	0.19%	0.22%	0.24%	0.27%	0.37%	0.39%	0.42%

I have tried lot's of things but can't seem to make this work, I know the issue is related to the LTV but can't understand why it is returning the row for 16% rather than 30%.

Any help would be greatly appreciated.

message edited by mecerrato


See More: Index/Match Array not returning some combinations correctly

Reply ↓  Report •

#1
August 18, 2018 at 17:42:52
This is a kludge work around, it simply extends DerbyDad's idea:

=IFERROR(INDEX(BB60:BI69,MATCH(IF(BC53>95%,97%,IF(BC53>90%,95%,IF(BC53>85%,90%,85%)))&BC54,AZ60:AZ69&BA60:BA69,-1),MATCH(IF(BC55>760,760,BC55),BB59:BI59,-1)),INDEX(BB60:BI69,MATCH(IF(BC53>95%,97%,BC53)&BC54,AZ60:AZ69&BA60:BA69,-1),MATCH(IF(BC55>760,760,BC55),BB59:BI59,-1)))*100

The problem is still the same,
when you combine the two numbers they convert to text, 0.920.3,
so when it increases value, it goes from 92 to 95 and never sees the 30,

This formula does the increase from 92 to 95 first.
It's not real elegant, but seems to work.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
August 18, 2018 at 19:05:00
Thanks for the suggestion but I feel like I will find another combination that won't work at a different LTV. I ended up creating another small table of all possible LTVs with the correct LTV to round to and then using a Vlookup to choose the correct LTV value. So instead of referring to the LTV entered by the user I take that user entered LTV and find it on the table and use the corresponding value from the vlookup formula. I didn't want an additional table but there was no escaping it.

=VLOOKUP(BM96,BS83:BT101,2)

	BS81	BS82
BR82	Need this to fix the LTV issue	
BR83	81%	85%
BR84	82%	85%
BR85	83%	85%
BR86	84%	85%
BR87	85%	85%
BR88	86%	90%
BR89	87%	90%
BR90	88%	90%
BR91	89%	90%
BR92	90%	90%
BR93	91%	95%
BR94	92%	95%
BR95	93%	95%
BR96	94%	95%
BR97	95%	95%
BR98	96%	97%
BR99	97%	97%
BR100	98%	97%
BR101	99%	97%


Reply ↓  Report •

#3
August 18, 2018 at 21:28:24
✔ Best Answer
The IF() statement does the same thing as your VLOOKUP().

Not sure why you have the IFERROR() at the beginning
but here is the complete formula with the extended IF() statement:

=IFERROR(INDEX(BB60:BI69,MATCH(IF(BC53>95%,97%,IF(BC53>90%,95%,IF(BC53>85%,90%,85%)))&BC54,AZ60:AZ69&BA60:BA69,-1),MATCH(IF(BC55>760,760,BC55),BB59:BI59,-1)),INDEX(BB60:BI69,MATCH(IF(BC53>95%,97%,IF(BC53>90%,95%,IF(BC53>85%,90%,85%)))&BC54,AZ60:AZ69&BA60:BA69,-1),MATCH(IF(BC55>760,760,BC55),BB59:BI59,-1)))*100


Not sure what parameters are allow for each entry, so is something like:

LTV 81%
Coverage 30%
Credit Score 680

Allowed?
Because this will return Row 8, which would be an incorrect number, 0.46.

We know the parameters for the LTV
but not for Coverage or Credit Score,
and since LTV and Coverage are related,
what are the possible combinations?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Related Solutions

#4
August 19, 2018 at 07:27:41
Rookie mistake on my behalf, I now adapted it to my cell ranges and it worked perfectly. I tried about 20 different combinations and it all resulted in correct results, thank you for your time, I appreciate it your help :-)

Reply ↓  Report •

#5
August 19, 2018 at 07:46:02
A person from the stackoverflow forum provided this formula that also worked:
=INDEX(BB60:BI69, AGGREGATE(14, 6, ROW(1:10)/((AZ60:AZ69>=MIN(BM96, MAX(AZ60:AZ69)))*(BA60:BA69>=BM97)), 1), IFERROR(MATCH(BM98, BB59:BI59, -1), 1))*100

I have never used the aggregate formula, I will have to read up on it because it looks interesting and I amy be able to adapt to other ideas.


Reply ↓  Report •

#6
August 19, 2018 at 08:39:12
The AGGREGATE() function is a 2010 addition and unfortunately I'm still on 2007,
but it does look like a handy function.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Ask Question