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

LTV 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

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

message edited by mmcconaghy

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%

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 680Allowed?

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

message edited by mmcconaghy

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

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))*100I 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.

The AGGREGATE() function is a 2010 addition and unfortunately I'm still on 2007,

but it does look like a handy function.MIKE

Ask Your Question

Weekly Poll

Do you think cloud computing will replacing gaming consoles?

Discuss in The Lounge

Poll History