ExcelBanter - Vlookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Vlookup (https://www.excelbanter.com/excel-discussion-misc-queries/149984-vlookup.html)

Mark

Vlookup

I'm using the vlookup function below and looking for the value in C27 that is
closest (numerically) to the value in columns W and returning the
corresponding value in X

=IF(C27"","",VLOOKUP(C27,$W$2:$X$98,2,TRUE))

C27 is -0.818

and the relavent part in W:X is

-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15

Using the vlookup as I have it returns 14, but the closest value is the one
corresponding to 14.5. How do I get it to return the value 14.5.

Vlookup

you may need to use a helper column (Y?)
in Y2 enter =abs(c$27-w2)
copy down to Y98
use index($X$2:$X$98,match(min($Y$2:$Y$98),$Y$2:$Y$98)

"Mark" wrote:

I'm using the vlookup function below and looking for the value in C27 that is
closest (numerically) to the value in columns W and returning the
corresponding value in X

=IF(C27"","",VLOOKUP(C27,$W$2:$X$98,2,TRUE))

C27 is -0.818

and the relavent part in W:X is

-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15

Using the vlookup as I have it returns 14, but the closest value is the one
corresponding to 14.5. How do I get it to return the value 14.5.

Mark

Vlookup

Thanks for your help, but I'm also using the vlookup to find a value in e27,
g27 which would mean I would need a column Z, AA, etc. Is there an easier
way?

"bj" wrote:

you may need to use a helper column (Y?)
in Y2 enter =abs(c$27-w2)
copy down to Y98
use index($X$2:$X$98,match(min($Y$2:$Y$98),$Y$2:$Y$98)

"Mark" wrote:

I'm using the vlookup function below and looking for the value in C27 that is
closest (numerically) to the value in columns W and returning the
corresponding value in X

=IF(C27"","",VLOOKUP(C27,$W$2:$X$98,2,TRUE))

C27 is -0.818

and the relavent part in W:X is

-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15

Using the vlookup as I have it returns 14, but the closest value is the one
corresponding to 14.5. How do I get it to return the value 14.5.

Mark

Vlookup

Mean -0.818 0.123 0.932 1.585
SD 0.132 0.250 0.273 0.211
M-1SD -0.950 -0.127 0.659 1.374
M+1SD -0.686 0.372 1.204 1.796
Returned 14 23 31.5 37.5
But Shd Be14.5 23.5 32 37.5

W X
-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15
-0.68078 15.5
....
-0.0099 22
0.03871 22.5
0.08702 23
0.13509 23.5
0.18291 24
0.23061 24.5
....
0.8506 31
0.90019 31.5
0.95039 32
1.00127 32.5
1.05301 33
1.10559 33.5
....
1.38651 36
1.44727 36.5
1.50992 37
1.57465 37.5
1.64175 38
1.71143 38.5
1.78403 39
....

"Mark" wrote:

Thanks for your help, but I'm also using the vlookup to find a value in e27,
g27 which would mean I would need a column Z, AA, etc. Is there an easier
way?

"bj" wrote:

you may need to use a helper column (Y?)
in Y2 enter =abs(c$27-w2)
copy down to Y98
use index($X$2:$X$98,match(min($Y$2:$Y$98),$Y$2:$Y$98)

"Mark" wrote:

I'm using the vlookup function below and looking for the value in C27 that is
closest (numerically) to the value in columns W and returning the
corresponding value in X

=IF(C27"","",VLOOKUP(C27,$W$2:$X$98,2,TRUE))

C27 is -0.818

and the relavent part in W:X is

-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15

Using the vlookup as I have it returns 14, but the closest value is the one
corresponding to 14.5. How do I get it to return the value 14.5.

Toppers

Vlookup

It's not too painful to repeat BJ's formulae:

in Y2 and copy across and down:

=ABS(C$27-$W2)

in C31 ("returned") and copy across:

=INDEX($X$2:$X$98,MATCH(MIN(Y$2:Y$98),Y$2:Y$98,0))

"Mark" wrote:

Mean -0.818 0.123 0.932 1.585
SD 0.132 0.250 0.273 0.211
M-1SD -0.950 -0.127 0.659 1.374
M+1SD -0.686 0.372 1.204 1.796
Returned 14 23 31.5 37.5
But Shd Be14.5 23.5 32 37.5

W X
-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15
-0.68078 15.5
...
-0.0099 22
0.03871 22.5
0.08702 23
0.13509 23.5
0.18291 24
0.23061 24.5
...
0.8506 31
0.90019 31.5
0.95039 32
1.00127 32.5
1.05301 33
1.10559 33.5
...
1.38651 36
1.44727 36.5
1.50992 37
1.57465 37.5
1.64175 38
1.71143 38.5
1.78403 39
...

"Mark" wrote:

Thanks for your help, but I'm also using the vlookup to find a value in e27,
g27 which would mean I would need a column Z, AA, etc. Is there an easier
way?

"bj" wrote:

you may need to use a helper column (Y?)
in Y2 enter =abs(c$27-w2)
copy down to Y98
use index($X$2:$X$98,match(min($Y$2:$Y$98),$Y$2:$Y$98)

"Mark" wrote:

I'm using the vlookup function below and looking for the value in C27 that is
closest (numerically) to the value in columns W and returning the
corresponding value in X

=IF(C27"","",VLOOKUP(C27,$W$2:$X$98,2,TRUE))

C27 is -0.818

and the relavent part in W:X is

-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15

Using the vlookup as I have it returns 14, but the closest value is the one
corresponding to 14.5. How do I get it to return the value 14.5.

Vlookup

another way would be to use
=index($x$2:$x$98,match(C27,$W$2:$W$98)+if(abs(c27-index($W$2:$W$98,match(C27,$W$2:$W$98) )abs(c27-index($W$2:$W$98,match(C27,$W$2:$W$98)+1),0,1)

or if you don't like long equations
name X2:X98 as XXX and W2:W98 as WWW

=index(XXX,match(C27,WWW)+if(abs(c27-index(WWW,match(C27,WWW)
)abs(c27-index(WWW,match(C27,WWW)+1),0,1)

or

=index(XXX,match(C27,WWW)+((c27-index(WWW,match(C27,WWW)
+1))=abs(c27-index(WWW,match(C27,WWW))))

"Mark" wrote:

Thanks for your help, but I'm also using the vlookup to find a value in e27,
g27 which would mean I would need a column Z, AA, etc. Is there an easier
way?

"bj" wrote:

you may need to use a helper column (Y?)
in Y2 enter =abs(c$27-w2)
copy down to Y98
use index($X$2:$X$98,match(min($Y$2:$Y$98),$Y$2:$Y$98)

"Mark" wrote:

I'm using the vlookup function below and looking for the value in C27 that is
closest (numerically) to the value in columns W and returning the
corresponding value in X

=IF(C27"","",VLOOKUP(C27,$W$2:$X$98,2,TRUE))

C27 is -0.818

and the relavent part in W:X is

-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15

Using the vlookup as I have it returns 14, but the closest value is the one
corresponding to 14.5. How do I get it to return the value 14.5.

Mark

Vlookup

This is the way I ended up doing it...

=IF(ABS(VLOOKUP(C27,$W$2:$X$98,1,TRUE)-C27)ABS(INDEX($W$2:$W$98,1+MATCH(C27,$W$2:$W$98,1 ))-C27),INDEX($X$2:$X$98,1+MATCH(C27,$W$2:$W$98,1)),V LOOKUP(C27,$W$2:$X$98,2,TRUE))

if appears to be similar to what bj did.

"bj" wrote:

another way would be to use
=index($x$2:$x$98,match(C27,$W$2:$W$98)+if(abs(c27-index($W$2:$W$98,match(C27,$W$2:$W$98) )abs(c27-index($W$2:$W$98,match(C27,$W$2:$W$98)+1),0,1)

or if you don't like long equations
name X2:X98 as XXX and W2:W98 as WWW

=index(XXX,match(C27,WWW)+if(abs(c27-index(WWW,match(C27,WWW)
)abs(c27-index(WWW,match(C27,WWW)+1),0,1)

or

=index(XXX,match(C27,WWW)+((c27-index(WWW,match(C27,WWW)
+1))=abs(c27-index(WWW,match(C27,WWW))))

"Mark" wrote:

Thanks for your help, but I'm also using the vlookup to find a value in e27,
g27 which would mean I would need a column Z, AA, etc. Is there an easier
way?

"bj" wrote:

you may need to use a helper column (Y?)
in Y2 enter =abs(c$27-w2)
copy down to Y98
use index($X$2:$X$98,match(min($Y$2:$Y$98),$Y$2:$Y$98)

"Mark" wrote:

I'm using the vlookup function below and looking for the value in C27 that is
closest (numerically) to the value in columns W and returning the
corresponding value in X

=IF(C27"","",VLOOKUP(C27,$W$2:$X$98,2,TRUE))

C27 is -0.818

and the relavent part in W:X is

-0.97233 13
-0.91169 13.5
-0.85234 14
-0.79413 14.5
-0.73697 15

Using the vlookup as I have it returns 14, but the closest value is the one
corresponding to 14.5. How do I get it to return the value 14.5.

All times are GMT +1. The time now is 09:15 PM.