Formula VLOOKUP I think
 

I have a set of numbers in column AG.

0.3411
0.5423
0.3310
0.9062
1.2282
0.8350
0.7205
0.8818
0.6557
0.6424
0.5358
0.4751
0.4319
0.3756
0.3713
0.3413 Formula here is in cell AK5207

I am playing with VLOOKUP to try and find the closest number from the
list which matches cell AG5207 which is 0.3413

Formula VLOOKUP I think
 

find the closest number from the list which
matches cell AG5207 which is 0.3413

What result do you expect? 0.3413 is in your list (unless I'm reading the
post incorrectly) so it is the closest match.


--
Biff
Microsoft Excel MVP


"rhhince" wrote in message
...
I have a set of numbers in column AG.

0.3411
0.5423
0.3310
0.9062
1.2282
0.8350
0.7205
0.8818
0.6557
0.6424
0.5358
0.4751
0.4319
0.3756
0.3713
0.3413 Formula here is in cell AK5207

I am playing with VLOOKUP to try and find the closest number from the
list which matches cell AG5207 which is 0.3413

Formula VLOOKUP I think
 

The result I am expecting is the number .3411 from the top of the list
to showup in cell AK5207



On Jan 16, 10:31 pm, "T. Valko" wrote:
find the closest number from the list which
matches cell AG5207 which is 0.3413

What result do you expect? 0.3413 is in your list (unless I'm reading the
post incorrectly) so it is the closest match.

--
Biff
Microsoft Excel MVP

"rhhince" wrote in message

...

I have a set of numbers in column AG.

0.3411
0.5423
0.3310
0.9062
1.2282
0.8350
0.7205
0.8818
0.6557
0.6424
0.5358
0.4751
0.4319
0.3756
0.3713
0.3413 Formula here is in cell AK5207

I am playing with VLOOKUP to try and find the closest number from the
list which matches cell AG5207 which is 0.3413

Formula VLOOKUP I think
 

Try this array formula** :

=INDEX(A1:A15,MATCH(MIN(ABS(A1:A15-D1)),ABS(A1:A15-D1),0))

Where D1 = 0.3413

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"rhhince" wrote in message
...
The result I am expecting is the number .3411 from the top of the list
to showup in cell AK5207



On Jan 16, 10:31 pm, "T. Valko" wrote:
find the closest number from the list which
matches cell AG5207 which is 0.3413

What result do you expect? 0.3413 is in your list (unless I'm reading the
post incorrectly) so it is the closest match.

--
Biff
Microsoft Excel MVP

"rhhince" wrote in message

...

I have a set of numbers in column AG.

0.3411
0.5423
0.3310
0.9062
1.2282
0.8350
0.7205
0.8818
0.6557
0.6424
0.5358
0.4751
0.4319
0.3756
0.3713
0.3413 Formula here is in cell AK5207

I am playing with VLOOKUP to try and find the closest number from the
list which matches cell AG5207 which is 0.3413