Matrix Lookup formula

What I am trying to do is choose a width and height. and populate with the number that is in the grid.

so in one cell A4 X B3 and then populate B4 224 and then so on for all the cells




Height 26 32 38 44 50 56 62 68 74 80 86 92 98 104 110 116 122
27 224 276 328 380 431 483 535 587 638 690 742 794 845 897 949 10011052
33 280 337 401 464 527 590 654 717 780 843 907 970 1033 1096 1160 1223 1286
39 331 399 473 548 623 698 772 847 922 997 1071 1146 1221 1296 1370 1445 1520
45 382 460 546 633 719 805 891 978 1064 1150 1236 1323 1409 1495 1581 1668 1754
51 433 521 619 717 815 912 1010 1108 1206 1303 1401 1499 1597 1694 1792 1890 1988
57 484 583 692 801 910 1020 1129 1238 1347 1457 1566 1675 1784 1894 2003 2112 2221
63 535 644 765 886 1006 1127 1248 1369 1489 1610 1731 1852 1972 2093 2214 2335 2455
69 586 705 838 970 1102 1234 1367 1499 1631 1763 1896 2028 2160 2292 2425 2557 2689
75 636 767 910 1054 1198 1342 1485 1629 1773 1917 2060 2204 2348 2492 2635 2779 2923
81 687 828 983 1139 1294 1449 1604 1760 1915 2070 2225 2381 2536 2691 2846 3002 3157
87 738 889 1056 1223 1390 1556 1723 1890 2057 2223 2390 2557 2724 2890 3057 3224 3391
99 840 1012 1202 1392 1581 1771 1961 2151 2340 2530 2720 2910 3099 3289 3479 3669 3858
110 933 1124 1335 1546 1757 1968 2179 2389 2600 2811 3022 3233 3444 3654 3865 4076 4287
122 1035 1247 1481 1715 1949 2182 2416 2650 2884 3118 3352 3585 3819 4053 4287 4521 4755

Originally Posted by JohnTopley

I suggest you change title to something like "Matrix Lookup formula": "help" is forbidden!!!

"Help with Matrix Lookup Formula" would be perfectly fine. "Help" is not forbidden, but vague thread titles are not much use to anyone.

title updated

Like JohnTopley's suggestion, copy this formula and paste into E22.

Formula:

=INDEX($B$4:$R$17,MATCH(D22,$A$4:$A$17,0),MATCH(C22,$B$3:$R$3,0))

That formula was for exact matches.

Try this. It does approximate matches which means the Heights and Widths must be sorted ascending.

=INDEX($B$4:$R$17,MATCH(D22,$A$4:$A$17,1),MATCH(C22,$B$3:$R$3,1))

If the Height you have is 40 inches it will return figures for 45 inches. Same for the Widths.

Edit No it won't. It will return figures for 39 inches.

If you want it to return figures for the 45 inch row array enter this formula

Formula:

=INDEX($B$4:$R$17,MATCH(1,1/($A$4:$A$17>=D22),0),MATCH(1,1/($B$3:$R$3>=C22),0))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.