#N/A Error with Index/Match Formula - Microsoft Community
i trying return value based on match multiple criteria (row 8 , column d, i.e. if row 8 percent 35.00% , column d 10.00%, return 5.90%). formula has been copied down , works exception of when match row 8 either 29.00% or 35.00% (note: 29.00 not shown). have validated cells formatted same percentages. here lookup table (not entire table includes column 35.00%
| d | e | f | g | h | i | j | ||
| loss ratio % | 39.01+ | 38.01-39.00 | 37.01-38.00 | 36.01-37.00 | 35.01-36.00 | 34.01-35.00 | ||
| 8 | growth % increase | 40.00% | 39.00% | 38.00% | 37.00% | 36.00% | 35.00% | |
| 9 | 15.0%+ | 15.00% | 0.00% | 6.10% | 6.60% | 7.30% | 7.80% | 8.40% |
| 10 | 10.0%-14.9% | 10.00% | 0.00% | 3.60% | 4.10% | 4.80% | 5.30% | 5.90% |
| 11 | 9.0%-9.9% | 9.00% | 0.00% | 3.40% | 3.90% | 4.60% | 5.10% | 5.70% |
| 12 | 8.0%-8.9% | 8.00% | 0.00% | 3.10% | 3.60% | 4.30% | 4.80% | 5.40% |
| 13 | 7.0%-7.9% | 7.00% | 0.00% | 2.90% | 3.40% | 4.10% | 4.60% | 5.20% |
| 14 | 6.0%-6.9% | 6.00% | 0.00% | 2.60% | 3.10% | 3.80% | 4.30% | 4.90% |
| 15 | 5.0%-5.9% | 5.00% | 0.00% | 2.40% | 2.90% | 3.60% | 4.10% | 4.70% |
| 16 | 4.0%-4.9% | 4.00% | 0.00% | 2.10% | 2.60% | 3.30% | 3.80% | 4.40% |
| 17 | 3.0%-3.9% | 3.00% | 0.00% | 1.90% | 2.40% | 3.10% | 3.60% | 4.20% |
| 18 | 2.0%-2.9% | 2.00% | 0.00% | 1.60% | 2.10% | 2.80% | 3.30% | 3.90% |
| 19 | 1.0%-1.9% | 1.00% | 0.00% | 1.40% | 1.90% | 2.60% | 3.10% | 3.70% |
| 20 | < 1.0% | 0.00% | 0.00% | 1.10% | 1.60% | 2.30% | 2.80% | 3.40% |
| an | ar | pgec |
| rounded | rounded | factor |
| 35.00% | 10.00% | #n/a |
| 32.00% | 2.00% | 5.70% |
| 26.00% | 4.00% | 11.65% |
| 40.00% | 0.00% | 0.00% |
| 26.00% | 0.00% | 10.65% |
| 29.00% | 9.00% | #n/a |
the formula can see returning #n/a when first column either 35.00% or 29.00% (does in situations part of excel file...works other percentages. here formula in cell returning #n/a:
=index('pgec table'!$e$9:$s$20,match(calculation!ar5,'pgec table'!$d$9:$d$20,0),match(calculation!an5,'pgec table'!$e$8:$s$8,0))
your match requires exact values - try sorting table first row 8 ascending, column d ascending, , change match true (sorted ascending) instead of false (exact match):
=index('pgec table'!$e$9:$s$20,match(calculation!ar5,'pgec table'!$d$9:$d$20,true),match(calculation!an5,'pgec table'!$e$8:$s$8,true))
Office / Excel / Other/unknown / Office 2010
Comments
Post a Comment