VLOOKUP value closest to

**dch27** · 09-10-2009, 09:27 AM

I have a row of data that I want to grab based on a single cell of data.

say my cell A1 = 5

I then have many rows of data in another worksheet:

8 | 100
7 | 93
6 | 73
9 | 32

since there is no value for "5" in any of the rows, I want to return the closest value to 5 (6). how do i do this?

**zbor** · 09-10-2009, 09:31 AM

First you must sort your left column in ascending order.
One way is to add in first column of the table 0 and 73 ( or =B3) wich will give you 73 for all positive values that you enter in A1

=VLOOKUP(A1;$A$2:$B$6;2;TRUE)

Please Login or Register  to view this content.

**NBVC** · 09-10-2009, 09:31 AM

What do you mean by closest... over or under or must be over....

Can the lookup sheet be sorted?

**DonkeyOte** · 09-10-2009, 09:33 AM

If as implied data is not sorted, criteria is in A1 on sheet1, values in Sheet2!A1:A4 then:

Please Login or Register  to view this content.

**dch27** · 09-10-2009, 09:36 AM

the data isnt sorted. when i mean closest, it can be over or under. keep in mind that these arent necessarily whole numbers. it could be:

6.1
6.2
5.1
3.3

in this case i would want 5.1

it could be

4.4
3.3
3.2
4.6
5.6

in this case i would want 4.6

**dch27** · 09-10-2009, 09:39 AM

donkey - that works. thanks

**zbor** · 09-10-2009, 09:41 AM

In that case your title does not comply Rule 1:

1. Your post title should accurately and concisely describe your problem, not your anticipated solution.

**DonkeyOte** · 09-10-2009, 09:47 AM

Originally Posted by dch27

donkey - that works. thanks

You mention VLOOKUP which would imply that perhaps you want to return the associated value - ie return B where A is closest match, you can alter the INDEX range to return the value from whichever column you wish, eg:

Please Login or Register  to view this content.

The Match needs to be conducted against A of course but the INDEX can be whichever column you prefer - alter as required.

**dch27** · 09-10-2009, 09:59 AM

another question:

because of the array that I am referencing is dynamic, sometimes the cells will say #N/A, and sometimes they dont. If at any time they do, Donkey's equation doesnt work because if these #N/A's. is there a way i can fix that.