# 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:AZ69BM97 (Coverage) Coverage Data Range is BA60:BA69BM98 (Credit Score) Credit Score Range is BB59:BI59Data Table to return value from is BB60:BI69Here 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 formulaWhen 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 beLTV 92%Coverage 30%Credit Score 680Here 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

#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)))*100The 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.MIKEmessage edited by mmcconaghy

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% ```

Report •

#3
August 18, 2018 at 21:28:24
 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)))*100Not sure what parameters are allow for each entry, so is something like:LTV 81%Coverage 30%Credit Score 680Allowed?Because this will return Row 8, which would be an incorrect number, 0.46.We know the parameters for the LTVbut not for Coverage or Credit Score,and since LTV and Coverage are related,what are the possible combinations?MIKEmessage edited by mmcconaghy

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 :-)

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.

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.MIKEhttp://www.skeptic.com/

Report •