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]

I think I am getting close but not that clear. So I have used the formula below and it gives me A4 instead of B5.

AC4=INDEX(C3:Z22,MATCH(AC2,B3:B22,1),MATCH(AC3,C2:Z2,1))

Then I used the below and had #N/A:

AC4=INDEX(C3:Z22,MATCH(AC2,B3:B22,-1),MATCH(AC3,C2:Z2,-1))

I need the big brains to help pull me out. Thanks again.
Kelly

 

[inactiveuserps07]

Kelly, you really don't need that big chart in order to get the value you want.

Even if you deleted the entire chart, the following formula would work in AC4 to give you what you're looking for:

=CHAR(64+CEILING(AC3,50)/50)&CEILING(AC2,5)/5

 

[Aladin Akyurek]

Generalizing from the single example you provide...

=INDEX($C$2:$Z$21,MATCH(AC2,$B$2:$B$21,1)+(LOOKUP(AC2,$B$2:$B$21) < AC2),MATCH(AC3,$C$1:$Z$1,1)+(LOOKUP(AC3,$C$1:$Z$1) < AC3))

Does this meet your intent?

 

[kelly mort]

Kelly, you really don't need that big chart in order to get the value you want.

Even if you deleted the entire chart, the following formula would work in AC4 to give you what you're looking for:

=CHAR(64+CEILING(AC3,50)/50)&CEILING(AC2,5)/5

wow!!! But I am a bit confused here. How does it work??? Because i will late put some REAL data into that chart. I hope you will explain to me how it does that. Thanks
Kelly

 

[kelly mort]

Generalizing from the single example you provide...

=INDEX($C$2:$Z$21,MATCH(AC2,$B$2:$B$21,1)+(LOOKUP(AC2,$B$2:$B$21) < AC2),MATCH(AC3,$C$1:$Z$1,1)+(LOOKUP(AC3,$C$1:$Z$1) < AC3))

Does this meet your intent?

Yes, it meets the intention!!!! Thanks alot
Kelly

 

[inactiveuserps07]

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.

 

[Aladin Akyurek]

Yes, it meets the intention!!!! Thanks alot
Kelly

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]

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.

For the numbers, that is the 5 to 100 in the first column and that of 50 to 1200, they will be static. But the A1, B1, etc; they are just testing data in the table. Will replace them letter. Your first formula was so short and got me more curious.
will try this one and report to you. Thanks
Kelly

 

[inactiveuserps07]

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