Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |