an approximate match function needed.

[kelly mort]

[TABLE="width: 728"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]850[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]1150[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]G1[/TD]
[TD]H1[/TD]
[TD]I1[/TD]
[TD]J1[/TD]
[TD]K1[/TD]
[TD]L1[/TD]
[TD]M1[/TD]
[TD]N1[/TD]
[TD]O1[/TD]
[TD]P1[/TD]
[TD]Q1[/TD]
[TD]R1[/TD]
[TD]S1[/TD]
[TD]T1[/TD]
[TD]U1[/TD]
[TD]V1[/TD]
[TD]W1[/TD]
[TD]X1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[TD]E2[/TD]
[TD]F2[/TD]
[TD]G2[/TD]
[TD]H1[/TD]
[TD]I2[/TD]
[TD]J2[/TD]
[TD]K2[/TD]
[TD]L2[/TD]
[TD]M2[/TD]
[TD]N2[/TD]
[TD]O2[/TD]
[TD]P2[/TD]
[TD]Q2[/TD]
[TD]R2[/TD]
[TD]S2[/TD]
[TD]T2[/TD]
[TD]U2[/TD]
[TD]V2[/TD]
[TD]W2[/TD]
[TD]X2[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[TD]D3[/TD]
[TD]E3[/TD]
[TD]F3[/TD]
[TD]G3[/TD]
[TD]H1[/TD]
[TD]I3[/TD]
[TD]J3[/TD]
[TD]K3[/TD]
[TD]L3[/TD]
[TD]M3[/TD]
[TD]N3[/TD]
[TD]O3[/TD]
[TD]P3[/TD]
[TD]Q3[/TD]
[TD]R3[/TD]
[TD]S3[/TD]
[TD]T3[/TD]
[TD]U3[/TD]
[TD]V3[/TD]
[TD]W3[/TD]
[TD]X3[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]A4[/TD]
[TD]B4[/TD]
[TD]C4[/TD]
[TD]D4[/TD]
[TD]E4[/TD]
[TD]F4[/TD]
[TD]G4[/TD]
[TD]H1[/TD]
[TD]I4[/TD]
[TD]J4[/TD]
[TD]K4[/TD]
[TD]L4[/TD]
[TD]M4[/TD]
[TD]N4[/TD]
[TD]O4[/TD]
[TD]P4[/TD]
[TD]Q4[/TD]
[TD]R4[/TD]
[TD]S4[/TD]
[TD]T4[/TD]
[TD]U4[/TD]
[TD]V4[/TD]
[TD]W4[/TD]
[TD]X4[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]A5[/TD]
[TD]B5[/TD]
[TD]C5[/TD]
[TD]D5[/TD]
[TD]E5[/TD]
[TD]F5[/TD]
[TD]G5[/TD]
[TD]H1[/TD]
[TD]I5[/TD]
[TD]J5[/TD]
[TD]K5[/TD]
[TD]L5[/TD]
[TD]M5[/TD]
[TD]N5[/TD]
[TD]O5[/TD]
[TD]P5[/TD]
[TD]Q1[/TD]
[TD]R5[/TD]
[TD]S5[/TD]
[TD]T5[/TD]
[TD]U5[/TD]
[TD]V5[/TD]
[TD]W5[/TD]
[TD]X5[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]A6[/TD]
[TD]B6[/TD]
[TD]C6[/TD]
[TD]D6[/TD]
[TD]E6[/TD]
[TD]F6[/TD]
[TD]G6[/TD]
[TD]H1[/TD]
[TD]I6[/TD]
[TD]J6[/TD]
[TD]K6[/TD]
[TD]L6[/TD]
[TD]M6[/TD]
[TD]N6[/TD]
[TD]O6[/TD]
[TD]P6[/TD]
[TD]Q2[/TD]
[TD]R6[/TD]
[TD]S6[/TD]
[TD]T6[/TD]
[TD]U6[/TD]
[TD]V6[/TD]
[TD]W6[/TD]
[TD]X6[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]A7[/TD]
[TD]B7[/TD]
[TD]C7[/TD]
[TD]D7[/TD]
[TD]E7[/TD]
[TD]F7[/TD]
[TD]G7[/TD]
[TD]H1[/TD]
[TD]I7[/TD]
[TD]J7[/TD]
[TD]K7[/TD]
[TD]L7[/TD]
[TD]M7[/TD]
[TD]N7[/TD]
[TD]O7[/TD]
[TD]P7[/TD]
[TD]Q3[/TD]
[TD]R7[/TD]
[TD]S7[/TD]
[TD]T7[/TD]
[TD]U7[/TD]
[TD]V7[/TD]
[TD]W7[/TD]
[TD]X7[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]A8[/TD]
[TD]B8[/TD]
[TD]C8[/TD]
[TD]D8[/TD]
[TD]E8[/TD]
[TD]F8[/TD]
[TD]G8[/TD]
[TD]H1[/TD]
[TD]I8[/TD]
[TD]J8[/TD]
[TD]K8[/TD]
[TD]L8[/TD]
[TD]M8[/TD]
[TD]N8[/TD]
[TD]O8[/TD]
[TD]P8[/TD]
[TD]Q4[/TD]
[TD]R8[/TD]
[TD]S8[/TD]
[TD]T8[/TD]
[TD]U8[/TD]
[TD]V8[/TD]
[TD]W8[/TD]
[TD]X8[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]A9[/TD]
[TD]B9[/TD]
[TD]C9[/TD]
[TD]D9[/TD]
[TD]E9[/TD]
[TD]F9[/TD]
[TD]G9[/TD]
[TD]H1[/TD]
[TD]I9[/TD]
[TD]J9[/TD]
[TD]K9[/TD]
[TD]L9[/TD]
[TD]M9[/TD]
[TD]N9[/TD]
[TD]O9[/TD]
[TD]P9[/TD]
[TD]Q1[/TD]
[TD]R9[/TD]
[TD]S9[/TD]
[TD]T9[/TD]
[TD]U9[/TD]
[TD]V9[/TD]
[TD]W9[/TD]
[TD]X9[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]A10[/TD]
[TD]B10[/TD]
[TD]C10[/TD]
[TD]D10[/TD]
[TD]E10[/TD]
[TD]F10[/TD]
[TD]G10[/TD]
[TD]H1[/TD]
[TD]I10[/TD]
[TD]J10[/TD]
[TD]K10[/TD]
[TD]L10[/TD]
[TD]M10[/TD]
[TD]N10[/TD]
[TD]O10[/TD]
[TD]P10[/TD]
[TD]Q2[/TD]
[TD]R10[/TD]
[TD]S10[/TD]
[TD]T10[/TD]
[TD]U10[/TD]
[TD]V10[/TD]
[TD]W10[/TD]
[TD]X10[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD]A11[/TD]
[TD]B11[/TD]
[TD]C11[/TD]
[TD]D11[/TD]
[TD]E11[/TD]
[TD]F11[/TD]
[TD]G11[/TD]
[TD]H1[/TD]
[TD]I11[/TD]
[TD]J11[/TD]
[TD]K11[/TD]
[TD]L11[/TD]
[TD]M11[/TD]
[TD]N11[/TD]
[TD]O11[/TD]
[TD]P11[/TD]
[TD]Q3[/TD]
[TD]R11[/TD]
[TD]S11[/TD]
[TD]T11[/TD]
[TD]U11[/TD]
[TD]V11[/TD]
[TD]W11[/TD]
[TD]X11[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD]A12[/TD]
[TD]B12[/TD]
[TD]C12[/TD]
[TD]D12[/TD]
[TD]E12[/TD]
[TD]F12[/TD]
[TD]G12[/TD]
[TD]H1[/TD]
[TD]I12[/TD]
[TD]J12[/TD]
[TD]K12[/TD]
[TD]L12[/TD]
[TD]M12[/TD]
[TD]N12[/TD]
[TD]O12[/TD]
[TD]P12[/TD]
[TD]Q4[/TD]
[TD]R12[/TD]
[TD]S12[/TD]
[TD]T12[/TD]
[TD]U12[/TD]
[TD]V12[/TD]
[TD]W12[/TD]
[TD]X12[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD]A13[/TD]
[TD]B13[/TD]
[TD]C13[/TD]
[TD]D13[/TD]
[TD]E13[/TD]
[TD]F13[/TD]
[TD]G13[/TD]
[TD]H1[/TD]
[TD]I13[/TD]
[TD]J13[/TD]
[TD]K13[/TD]
[TD]L13[/TD]
[TD]M13[/TD]
[TD]N13[/TD]
[TD]O13[/TD]
[TD]P13[/TD]
[TD]Q1[/TD]
[TD]R13[/TD]
[TD]S13[/TD]
[TD]T13[/TD]
[TD]U13[/TD]
[TD]V13[/TD]
[TD]W13[/TD]
[TD]X13[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD]A14[/TD]
[TD]B14[/TD]
[TD]C14[/TD]
[TD]D14[/TD]
[TD]E14[/TD]
[TD]F14[/TD]
[TD]G14[/TD]
[TD]H1[/TD]
[TD]I14[/TD]
[TD]J14[/TD]
[TD]K14[/TD]
[TD]L14[/TD]
[TD]M14[/TD]
[TD]N14[/TD]
[TD]O14[/TD]
[TD]P14[/TD]
[TD]Q2[/TD]
[TD]R14[/TD]
[TD]S14[/TD]
[TD]T14[/TD]
[TD]U14[/TD]
[TD]V14[/TD]
[TD]W14[/TD]
[TD]X14[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD]A15[/TD]
[TD]B15[/TD]
[TD]C15[/TD]
[TD]D15[/TD]
[TD]E15[/TD]
[TD]F15[/TD]
[TD]G15[/TD]
[TD]H1[/TD]
[TD]I15[/TD]
[TD]J15[/TD]
[TD]K15[/TD]
[TD]L15[/TD]
[TD]M15[/TD]
[TD]N15[/TD]
[TD]O15[/TD]
[TD]P15[/TD]
[TD]Q3[/TD]
[TD]R15[/TD]
[TD]S15[/TD]
[TD]T15[/TD]
[TD]U15[/TD]
[TD]V15[/TD]
[TD]W15[/TD]
[TD]X15[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]A16[/TD]
[TD]B16[/TD]
[TD]C16[/TD]
[TD]D16[/TD]
[TD]E16[/TD]
[TD]F16[/TD]
[TD]G16[/TD]
[TD]H1[/TD]
[TD]I16[/TD]
[TD]J16[/TD]
[TD]K16[/TD]
[TD]L16[/TD]
[TD]M16[/TD]
[TD]N16[/TD]
[TD]O16[/TD]
[TD]P16[/TD]
[TD]Q4[/TD]
[TD]R16[/TD]
[TD]S16[/TD]
[TD]T16[/TD]
[TD]U16[/TD]
[TD]V16[/TD]
[TD]W16[/TD]
[TD]X16[/TD]
[/TR]
[TR]
[TD="align: right"]85[/TD]
[TD]A17[/TD]
[TD]B17[/TD]
[TD]C17[/TD]
[TD]D17[/TD]
[TD]E17[/TD]
[TD]F17[/TD]
[TD]G17[/TD]
[TD]H1[/TD]
[TD]I17[/TD]
[TD]J17[/TD]
[TD]K17[/TD]
[TD]L17[/TD]
[TD]M17[/TD]
[TD]N17[/TD]
[TD]O17[/TD]
[TD]P17[/TD]
[TD]Q1[/TD]
[TD]R17[/TD]
[TD]S17[/TD]
[TD]T17[/TD]
[TD]U17[/TD]
[TD]V17[/TD]
[TD]W17[/TD]
[TD]X17[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD]A18[/TD]
[TD]B18[/TD]
[TD]C18[/TD]
[TD]D18[/TD]
[TD]E18[/TD]
[TD]F18[/TD]
[TD]G18[/TD]
[TD]H1[/TD]
[TD]I18[/TD]
[TD]J18[/TD]
[TD]K18[/TD]
[TD]L18[/TD]
[TD]M18[/TD]
[TD]N18[/TD]
[TD]O18[/TD]
[TD]P18[/TD]
[TD]Q2[/TD]
[TD]R18[/TD]
[TD]S18[/TD]
[TD]T18[/TD]
[TD]U18[/TD]
[TD]V18[/TD]
[TD]W18[/TD]
[TD]X18[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD]A19[/TD]
[TD]B19[/TD]
[TD]C19[/TD]
[TD]D19[/TD]
[TD]E19[/TD]
[TD]F19[/TD]
[TD]G19[/TD]
[TD]H1[/TD]
[TD]I19[/TD]
[TD]J19[/TD]
[TD]K19[/TD]
[TD]L19[/TD]
[TD]M19[/TD]
[TD]N19[/TD]
[TD]O19[/TD]
[TD]P19[/TD]
[TD]Q3[/TD]
[TD]R19[/TD]
[TD]S19[/TD]
[TD]T19[/TD]
[TD]U19[/TD]
[TD]V19[/TD]
[TD]W19[/TD]
[TD]X19[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD]A20[/TD]
[TD]B20[/TD]
[TD]C20[/TD]
[TD]D20[/TD]
[TD]E20[/TD]
[TD]F20[/TD]
[TD]G20[/TD]
[TD]H1[/TD]
[TD]I20[/TD]
[TD]J20[/TD]
[TD]K20[/TD]
[TD]L20[/TD]
[TD]M20[/TD]
[TD]N20[/TD]
[TD]O20[/TD]
[TD]P20[/TD]
[TD]Q4[/TD]
[TD]R20[/TD]
[TD]S20[/TD]
[TD]T20[/TD]
[TD]U20[/TD]
[TD]V20[/TD]
[TD]W20[/TD]
[TD]X20 [/TD]
[/TR]
</tbody>[/TABLE]

I have the table above in my worksheet and i need a lookup formula that will use approximate match to get values from the table. The table takes the range; B2:Z22. Then in AC2 I have a value there say 22 and in AC3 another value say 89. Then I want the match pick B5 from the Table and show it in AC4. I hope my point is clear for understanding. Thanks very much in advance.
Kelly

 

[kelly mort]

Hi, Kelly. The first formula would only work with the exact data you gave in the example data set.

If you are going to use other data that isn't letter-number combinations, use this version in AC3:

=INDEX($C$3:$Z$22,CEILING($AC$2,5)/5,CEILING($AC$3,50)/50)

CEILING() just rounds up to the nearest number you use as the second argument. Since your rows are numbered in 5s and your columns are numbered in 50s, we just round your input cell AC2 up to the nearest 5 and divide by 5 to get the INDEX row, and round up AC3 to the nearest 50 then divide by 50 to get the INDEX column.

Oh, Okay I have now understood your formula!!!. Thanks for it. But the first too seem to be working fine still.

 

[kelly mort]

You are welcome. Note that the set up is generic to the row headers and column headers which are both sorted in ascending order.

 

[kelly mort]

Yes, it's short and will work no matter what data you place in your INDEX range.

 

[inactiveuserps07]

@kelly mort, better to have too many solutions than none at all. Glad it worked out for you.