an approximate match function needed.

[kelly mort]

50

100

150

200

250

300

350

400

450

500

550

600

650

700

750

800

850

900

950

1000

1050

1100

1150

1200

5

A1

B1

C1

D1

E1

F1

G1

H1

I1

J1

K1

L1

M1

N1

O1

P1

Q1

R1

S1

T1

U1

V1

W1

X1

10

A2

B2

C2

D2

E2

F2

G2

H1

I2

J2

K2

L2

M2

N2

O2

P2

Q2

R2

S2

T2

U2

V2

W2

X2

15

A3

B3

C3

D3

E3

F3

G3

H1

I3

J3

K3

L3

M3

N3

O3

P3

Q3

R3

S3

T3

U3

V3

W3

X3

20

A4

B4

C4

D4

E4

F4

G4

H1

I4

J4

K4

L4

M4

N4

O4

P4

Q4

R4

S4

T4

U4

V4

W4

X4

25

A5

B5

C5

D5

E5

F5

G5

H1

I5

J5

K5

L5

M5

N5

O5

P5

Q1

R5

S5

T5

U5

V5

W5

X5

30

A6

B6

C6

D6

E6

F6

G6

H1

I6

J6

K6

L6

M6

N6

O6

P6

Q2

R6

S6

T6

U6

V6

W6

X6

35

A7

B7

C7

D7

E7

F7

G7

H1

I7

J7

K7

L7

M7

N7

O7

P7

Q3

R7

S7

T7

U7

V7

W7

X7

40

A8

B8

C8

D8

E8

F8

G8

H1

I8

J8

K8

L8

M8

N8

O8

P8

Q4

R8

S8

T8

U8

V8

W8

X8

45

A9

B9

C9

D9

E9

F9

G9

H1

I9

J9

K9

L9

M9

N9

O9

P9

Q1

R9

S9

T9

U9

V9

W9

X9

50

A10

B10

C10

D10

E10

F10

G10

H1

I10

J10

K10

L10

M10

N10

O10

P10

Q2

R10

S10

T10

U10

V10

W10

X10

55

A11

B11

C11

D11

E11

F11

G11

H1

I11

J11

K11

L11

M11

N11

O11

P11

Q3

R11

S11

T11

U11

V11

W11

X11

60

A12

B12

C12

D12

E12

F12

G12

H1

I12

J12

K12

L12

M12

N12

O12

P12

Q4

R12

S12

T12

U12

V12

W12

X12

65

A13

B13

C13

D13

E13

F13

G13

H1

I13

J13

K13

L13

M13

N13

O13

P13

Q1

R13

S13

T13

U13

V13

W13

X13

70

A14

B14

C14

D14

E14

F14

G14

H1

I14

J14

K14

L14

M14

N14

O14

P14

Q2

R14

S14

T14

U14

V14

W14

X14

75

A15

B15

C15

D15

E15

F15

G15

H1

I15

J15

K15

L15

M15

N15

O15

P15

Q3

R15

S15

T15

U15

V15

W15

X15

80

A16

B16

C16

D16

E16

F16

G16

H1

I16

J16

K16

L16

M16

N16

O16

P16

Q4

R16

S16

T16

U16

V16

W16

X16

85

A17

B17

C17

D17

E17

F17

G17

H1

I17

J17

K17

L17

M17

N17

O17

P17

Q1

R17

S17

T17

U17

V17

W17

X17

90

A18

B18

C18

D18

E18

F18

G18

H1

I18

J18

K18

L18

M18

N18

O18

P18

Q2

R18

S18

T18

U18

V18

W18

X18

95

A19

B19

C19

D19

E19

F19

G19

H1

I19

J19

K19

L19

M19

N19

O19

P19

Q3

R19

S19

T19

U19

V19

W19

X19

100

A20

B20

C20

D20

E20

F20

G20

H1

I20

J20

K20

L20

M20

N20

O20

P20

Q4

R20

S20

T20

U20

V20

W20

X20

<tbody>
</tbody>

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.