JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Approximate Value Formula
Hi ALL,
I hope someone can help me with this problem. What im trying to do it to look up the approximate value of Tab 1 column 1 from Tab 2 column 2. Below are the formula that i have used so far:
=MATCH(MIN(ABS(Ratings!A:B-E9)),ABS(Ratings!A:B-E9),0)
=VLOOKUP(E10,Ratings!C:D,2,TRUE)
Both formula arent working.
See tables below as in example
Tab 1:
Actual NPS</SPAN>
SC Value</SPAN>
33.33</SPAN>
50.48</SPAN>
17.01</SPAN>
0.00</SPAN>
<TBODY>
</TBODY>
Tab 2:
57.00
5
56.945
4.99
56.89
4.98
56.835
4.97
56.78
4.96
56.725
4.95
56.67
4.94
56.615
4.93
<TBODY>
</TBODY>
Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
so you are looking up 33.33 in a range of numbers from 56.615 to 57
are you trying to use y= mx +c approach eg find the equation that gives y 56.615 for x value of 4.93 etc
it could be y=mx+c or y = nx^2 +mx +c or far more complicated
so you are looking up 33.33 in a range of numbers from 56.615 to 57
are you trying to use y= mx +c approach eg find the equation that gives y 56.615 for x value of 4.93 etc
it could be y=mx+c or y = nx^2 +mx +c or far more complicated
Hi Oldbrewer,
The table is just an example but it goes down further.
To use VLOOKUP for an approximate match the lookup table must be sorted ascending by the first column.
Hi Andrew Poulsom,
Thank you for the suggesttion
i tried arranging the first column in an ascending manner but i got a value error.
Same as indicated in the posted question
You need to avoid using entire columns. Here's an example:
Excel 2010 C D E F 1 56.615 4.93 2 56.67 4.94 3 56.725 4.95 4 56.78 4.96 5 56.835 4.97 6 56.89 4.98 7 56.945 4.99 8 57 5 9 10 56.8 4.96
Hi Andrew,
I did the sample as stated above and i got a value error again. See full table below:
2 -5 2.055 -4.99 2.11 -4.98 2.165 -4.97 2.22 -4.96 2.275 -4.95 2.33 -4.94 2.385 -4.93 2.44 -4.92 2.495 -4.91 2.55 -4.9 2.605 -4.89 2.66 -4.88 2.715 -4.87 2.77 -4.86 2.825 -4.85 2.88 -4.84 2.935 -4.83 2.99 -4.82 3.045 -4.81 3.1 -4.8 3.155 -4.79 3.21 -4.78 3.265 -4.77 3.32 -4.76 3.375 -4.75 3.43 -4.74 3.485 -4.73 3.54 -4.72 3.595 -4.71 3.65 -4.7 3.705 -4.69 3.76 -4.68 3.815 -4.67 3.87 -4.66 3.925 -4.65 3.98 -4.64 4.035 -4.63 4.09 -4.62 4.145 -4.61 4.2 -4.6 4.255 -4.59 4.31 -4.58 4.365 -4.57 4.42 -4.56 4.475 -4.55 4.53 -4.54 4.585 -4.53 4.64 -4.52 4.695 -4.51 4.75 -4.5 4.805 -4.49 4.86 -4.48 4.915 -4.47 4.97 -4.46 5.025 -4.45 5.08 -4.44 5.135 -4.43 5.19 -4.42 5.245 -4.41 5.3 -4.4 5.355 -4.39 5.41 -4.38 5.465 -4.37 5.52 -4.36 5.575 -4.35 5.63 -4.34 5.685 -4.33 5.74 -4.32 5.795 -4.31 5.85 -4.3 5.905 -4.29 5.96 -4.28 6.015 -4.27 6.07 -4.26 6.125 -4.25 6.18 -4.24 6.235 -4.23 6.29 -4.22 6.345 -4.21 6.4 -4.2 6.455 -4.19 6.51 -4.18 6.565 -4.17 6.62 -4.16 6.675 -4.15 6.73 -4.14 6.785 -4.13 6.84 -4.12 6.895 -4.11 6.95 -4.1 7.005 -4.09 7.06 -4.08 7.115 -4.07 7.17 -4.06 7.225 -4.05 7.28 -4.04 7.335 -4.03 7.39 -4.02 7.445 -4.01 7.5 -4 7.555 -3.99 7.61 -3.98 7.665 -3.97 7.72 -3.96 7.775 -3.95 7.83 -3.94 7.885 -3.93 7.94 -3.92 7.995 -3.91 8.05 -3.9 8.105 -3.89 8.16 -3.88 8.215 -3.87 8.27 -3.86 8.325 -3.85 8.38 -3.84 8.435 -3.83 8.49 -3.82 8.545 -3.81 8.6 -3.8 8.655 -3.79 8.71 -3.78 8.765 -3.77 8.82 -3.76 8.875 -3.75 8.93 -3.74 8.985 -3.73 9.04 -3.72 9.095 -3.71 9.15 -3.7 9.205 -3.69 9.26 -3.68 9.315 -3.67 9.37 -3.66 9.425 -3.65 9.48 -3.64 9.535 -3.63 9.59 -3.62 9.645 -3.61 9.7 -3.6 9.755 -3.59 9.81 -3.58 9.865 -3.57 9.92 -3.56 9.975 -3.55 10.03 -3.54 10.085 -3.53 10.14 -3.52 10.195 -3.51 10.25 -3.5 10.305 -3.49 10.36 -3.48 10.415 -3.47 10.47 -3.46 10.525 -3.45 10.58 -3.44 10.635 -3.43 10.69 -3.42 10.745 -3.41 10.8 -3.4 10.855 -3.39 10.91 -3.38 10.965 -3.37 11.02 -3.36 11.075 -3.35 11.13 -3.34 11.185 -3.33 11.24 -3.32 11.295 -3.31 11.35 -3.3 11.405 -3.29 11.46 -3.28 11.515 -3.27 11.57 -3.26 11.625 -3.25 11.68 -3.24 11.735 -3.23 11.79 -3.22 11.845 -3.21 11.9 -3.2 11.955 -3.19 12.01 -3.18 12.065 -3.17 12.12 -3.16 12.175 -3.15 12.23 -3.14 12.285 -3.13 12.34 -3.12 12.395 -3.11 12.45 -3.1 12.505 -3.09 12.56 -3.08 12.615 -3.07 12.67 -3.06 12.725 -3.05 12.78 -3.04 12.835 -3.03 12.89 -3.02 12.945 -3.01 13 -3 13.055 -2.99 13.11 -2.98 13.165 -2.97 13.22 -2.96 13.275 -2.95 13.33 -2.94 13.385 -2.93 13.44 -2.92 13.495 -2.91 13.55 -2.9 13.605 -2.89 13.66 -2.88 13.715 -2.87 13.77 -2.86 13.825 -2.85 13.88 -2.84 13.935 -2.83 13.99 -2.82 14.045 -2.81 14.1 -2.8 14.155 -2.79 14.21 -2.78 14.265 -2.77 14.32 -2.76 14.375 -2.75 14.43 -2.74 14.485 -2.73 14.54 -2.72 14.595 -2.71 14.65 -2.7 14.705 -2.69 14.76 -2.68 14.815 -2.67 14.87 -2.66 14.925 -2.65 14.98 -2.64 15.035 -2.63 15.09 -2.62 15.145 -2.61 15.2 -2.6 15.255 -2.59 15.31 -2.58 15.365 -2.57 15.42 -2.56 15.475 -2.55 15.53 -2.54 15.585 -2.53 15.64 -2.52 15.695 -2.51 15.75 -2.5 15.805 -2.49 15.86 -2.48 15.915 -2.47 15.97 -2.46 16.025 -2.45 16.08 -2.44 16.135 -2.43 16.19 -2.42 16.245 -2.41 16.3 -2.4 16.355 -2.39 16.41 -2.38 16.465 -2.37 16.52 -2.36 16.575 -2.35 16.63 -2.34 16.685 -2.33 16.74 -2.32 16.795 -2.31 16.85 -2.3 16.905 -2.29 16.96 -2.28 17.015 -2.27 17.07 -2.26 17.125 -2.25 17.18 -2.24 17.235 -2.23 17.29 -2.22 17.345 -2.21 17.4 -2.2 17.455 -2.19 17.51 -2.18 17.565 -2.17 17.62 -2.16 17.675 -2.15 17.73 -2.14 17.785 -2.13 17.84 -2.12 17.895 -2.11 17.95 -2.1 18.005 -2.09 18.06 -2.08 18.115 -2.07 18.17 -2.06 18.225 -2.05 18.28 -2.04 18.335 -2.03 18.39 -2.02 18.445 -2.01 18.5 -2 18.555 -1.99 18.61 -1.98 18.665 -1.97 18.72 -1.96 18.775 -1.95 18.83 -1.94 18.885 -1.93 18.94 -1.92 18.995 -1.91 19.05 -1.9 19.105 -1.89 19.16 -1.88 19.215 -1.87 19.27 -1.86 19.325 -1.85 19.38 -1.84 19.435 -1.83 19.49 -1.82 19.545 -1.81 19.6 -1.8 19.655 -1.79 19.71 -1.78 19.765 -1.77 19.82 -1.76 19.875 -1.75 19.93 -1.74 19.985 -1.73 20.04 -1.72 20.095 -1.71 20.15 -1.7 20.205 -1.69 20.26 -1.68 20.315 -1.67 20.37 -1.66 20.425 -1.65 20.48 -1.64 20.535 -1.63 20.59 -1.62 20.645 -1.61 20.7 -1.6 20.755 -1.59 20.81 -1.58 20.865 -1.57 20.92 -1.56 20.975 -1.55 21.03 -1.54 21.085 -1.53 21.14 -1.52 21.195 -1.51 21.25 -1.5 21.305 -1.49 21.36 -1.48 21.415 -1.47 21.47 -1.46 21.525 -1.45 21.58 -1.44 21.635 -1.43 21.69 -1.42 21.745 -1.41 21.8 -1.4 21.855 -1.39 21.91 -1.38 21.965 -1.37 22.02 -1.36 22.075 -1.35 22.13 -1.34 22.185 -1.33 22.24 -1.32 22.295 -1.31 22.35 -1.3 22.405 -1.29 22.46 -1.28 22.515 -1.27 22.57 -1.26 22.625 -1.25 22.68 -1.24 22.735 -1.23 22.79 -1.22 22.845 -1.21 22.9 -1.2 22.955 -1.19 23.01 -1.18 23.065 -1.17 23.12 -1.16 23.175 -1.15 23.23 -1.14 23.285 -1.13 23.34 -1.12 23.395 -1.11 23.45 -1.1 23.505 -1.09 23.56 -1.08 23.615 -1.07 23.67 -1.06 23.725 -1.05 23.78 -1.04 23.835 -1.03 23.89 -1.02 23.945 -1.01 24 -1 24.055 -0.99 24.11 -0.98 24.165 -0.97 24.22 -0.96 24.275 -0.95 24.33 -0.94 24.385 -0.93 24.44 -0.92 24.495 -0.91 24.55 -0.9 24.605 -0.89 24.66 -0.88 24.715 -0.87 24.77 -0.86 24.825 -0.85 24.88 -0.84 24.935 -0.83 24.99 -0.82 25.045 -0.81 25.1 -0.8 25.155 -0.79 25.21 -0.78 25.265 -0.77 25.32 -0.76 25.375 -0.75 25.43 -0.74 25.485 -0.73 25.54 -0.72 25.595 -0.71 25.65 -0.7 25.705 -0.69 25.76 -0.68 25.815 -0.67 25.87 -0.66 25.925 -0.65 25.98 -0.64 26.035 -0.63 26.09 -0.62 26.145 -0.61 26.2 -0.6 26.255 -0.59 26.31 -0.58 26.365 -0.57 26.42 -0.56 26.475 -0.55 26.53 -0.54 26.585 -0.53 26.64 -0.52 26.695 -0.51 26.75 -0.5 26.805 -0.49 26.86 -0.48 26.915 -0.47 26.97 -0.46 27.025 -0.45 27.08 -0.44 27.135 -0.43 27.19 -0.42 27.245 -0.41 27.3 -0.4 27.355 -0.39 27.41 -0.38 27.465 -0.37 27.52 -0.36 27.575 -0.35 27.63 -0.34 27.685 -0.33 27.74 -0.32 27.795 -0.31 27.85 -0.3 27.905 -0.29 27.96 -0.28 28.015 -0.27 28.07 -0.26 28.125 -0.25 28.18 -0.24 28.235 -0.23 28.29 -0.22 28.345 -0.21 28.4 -0.2 28.455 -0.19 28.51 -0.18 28.565 -0.17 28.62 -0.16 28.675 -0.15 28.73 -0.14 28.785 -0.13 28.84 -0.12 28.895 -0.11 28.95 -0.1 29.005 -0.09 29.06 -0.08 29.115 -0.07 29.17 -0.06 29.225 -0.05 29.28 -0.04 29.335 -0.03 29.39 -0.02 29.445 -0.01 29.5 1.1E-13 29.555 0.01 29.61 0.02 29.665 0.03 29.72 0.04 29.775 0.05 29.83 0.06 29.885 0.07 29.94 0.08 29.995 0.09 30.05 0.1 30.105 0.11 30.16 0.12 30.215 0.13 30.27 0.14 30.325 0.15 30.38 0.16 30.435 0.17 30.49 0.18 30.545 0.19 30.6 0.2 30.655 0.21 30.71 0.22 30.765 0.23 30.82 0.24 30.875 0.25 30.93 0.26 30.985 0.27 31.04 0.28 31.095 0.29 31.15 0.3 31.205 0.31 31.26 0.32 31.315 0.33 31.37 0.34 31.425 0.35 31.48 0.36 31.535 0.37 31.59 0.38 31.645 0.39 31.7 0.4 31.755 0.41 31.81 0.42 31.865 0.43 31.92 0.44 31.975 0.45 32.03 0.46 32.085 0.47 32.14 0.48 32.195 0.49 32.25 0.5 32.305 0.51 32.36 0.52 32.415 0.53 32.47 0.54 32.525 0.55 32.58 0.56 32.635 0.57 32.69 0.58 32.745 0.59 32.8 0.6 32.855 0.61 32.91 0.62 32.965 0.63 33.02 0.64 33.075 0.65 33.13 0.66 33.185 0.67 33.24 0.68 33.295 0.69 33.35 0.7 33.405 0.71 33.46 0.72 33.515 0.73 33.57 0.74 33.625 0.75 33.68 0.76 33.735 0.77 33.79 0.78 33.845 0.79 33.9 0.8 33.955 0.81 34.01 0.82 34.065 0.83 34.12 0.84 34.175 0.85 34.23 0.86 34.285 0.87 34.34 0.88 34.395 0.89 34.45 0.9 34.505 0.91 34.56 0.92 34.615 0.93 34.67 0.94 34.725 0.95 34.78 0.96 34.835 0.97 34.89 0.98 34.945 0.99 35 1 35.055 1.01 35.11 1.02 35.165 1.03 35.22 1.04 35.275 1.05 35.33 1.06 35.385 1.07 35.44 1.08 35.495 1.09 35.55 1.1 35.605 1.11 35.66 1.12 35.715 1.13 35.77 1.14 35.825 1.15 35.88 1.16 35.935 1.17 35.99 1.18 36.045 1.19 36.1 1.2 36.155 1.21 36.21 1.22 36.265 1.23 36.32 1.24 36.375 1.25 36.43 1.26 36.485 1.27 36.54 1.28 36.595 1.29 36.65 1.3 36.705 1.31 36.76 1.32 36.815 1.33 36.87 1.34 36.925 1.35 36.98 1.36 37.035 1.37 37.09 1.38 37.145 1.39 37.2 1.4 37.255 1.41 37.31 1.42 37.365 1.43 37.42 1.44 37.475 1.45 37.53 1.46 37.585 1.47 37.64 1.48 37.695 1.49 37.75 1.5 37.805 1.51 37.86 1.52 37.915 1.53 37.97 1.54 38.025 1.55 38.08 1.56 38.135 1.57 38.19 1.58 38.245 1.59 38.3 1.6 38.355 1.61 38.41 1.62 38.465 1.63 38.52 1.64 38.575 1.65 38.63 1.66 38.685 1.67 38.74 1.68 38.795 1.69 38.85 1.7 38.905 1.71 38.96 1.72 39.015 1.73 39.07 1.74 39.125 1.75 39.18 1.76 39.235 1.77 39.29 1.78 39.345 1.79 39.4 1.8 39.455 1.81 39.51 1.82 39.565 1.83 39.62 1.84 39.675 1.85 39.73 1.86 39.785 1.87 39.84 1.88 39.895 1.89 39.95 1.9 40.005 1.91 40.06 1.92 40.115 1.93 40.17 1.94 40.225 1.95 40.28 1.96 40.335 1.97 40.39 1.98 40.445 1.99 40.5 2 40.555 2.01 40.61 2.02 40.665 2.03 40.72 2.04 40.775 2.05 40.83 2.06 40.885 2.07 40.94 2.08 40.995 2.09 41.05 2.1 41.105 2.11 41.16 2.12 41.215 2.13 41.27 2.14 41.325 2.15 41.38 2.16 41.435 2.17 41.49 2.18 41.545 2.19 41.6 2.2 41.655 2.21 41.71 2.22 41.765 2.23 41.82 2.24 41.875 2.25 41.93 2.26 41.985 2.27 42.04 2.28 42.095 2.29 42.15 2.3 42.205 2.31 42.26 2.32 42.315 2.33 42.37 2.34 42.425 2.35 42.48 2.36 42.535 2.37 42.59 2.38 42.645 2.39 42.7 2.4 42.755 2.41 42.81 2.42 42.865 2.43 42.92 2.44 42.975 2.45 43.03 2.46 43.085 2.47 43.14 2.48 43.195 2.49 43.25 2.5 43.305 2.51 43.36 2.52 43.415 2.53 43.47 2.54 43.525 2.55 43.58 2.56 43.635 2.57 43.69 2.58 43.745 2.59 43.8 2.6 43.855 2.61 43.91 2.62 43.965 2.63 44.02 2.64 44.075 2.65 44.13 2.66 44.185 2.67 44.24 2.68 44.295 2.69 44.35 2.7 44.405 2.71 44.46 2.72 44.515 2.73 44.57 2.74 44.625 2.75 44.68 2.76 44.735 2.77 44.79 2.78 44.845 2.79 44.9 2.8 44.955 2.81 45.01 2.82 45.065 2.83 45.12 2.84 45.175 2.85 45.23 2.86 45.285 2.87 45.34 2.88 45.395 2.89 45.45 2.9 45.505 2.91 45.56 2.92 45.615 2.93 45.67 2.94 45.725 2.95 45.78 2.96 45.835 2.97 45.89 2.98 45.945 2.99 46 3 46.055 3.01 46.11 3.02 46.165 3.03 46.22 3.04 46.275 3.05 46.33 3.06 46.385 3.07 46.44 3.08 46.495 3.09 46.55 3.1 46.605 3.11 46.66 3.12 46.715 3.13 46.77 3.14 46.825 3.15 46.88 3.16 46.935 3.17 46.99 3.18 47.045 3.19 47.1 3.2 47.155 3.21 47.21 3.22 47.265 3.23 47.32 3.24 47.375 3.25 47.43 3.26 47.485 3.27 47.54 3.28 47.595 3.29 47.65 3.3 47.705 3.31 47.76 3.32 47.815 3.33 47.87 3.34 47.925 3.35 47.98 3.36 48.035 3.37 48.09 3.38 48.145 3.39 48.2 3.4 48.255 3.41 48.31 3.42 48.365 3.43 48.42 3.44 48.475 3.45 48.53 3.46 48.585 3.47 48.64 3.48 48.695 3.49 48.75 3.5 48.805 3.51 48.86 3.52 48.915 3.53 48.97 3.54 49.025 3.55 49.08 3.56 49.135 3.57 49.19 3.58 49.245 3.59 49.3 3.6 49.355 3.61 49.41 3.62 49.465 3.63 49.52 3.64 49.575 3.65 49.63 3.66 49.685 3.67 49.74 3.68 49.795 3.69 49.85 3.7 49.905 3.71 49.96 3.72 50.015 3.73 50.07 3.74 50.125 3.75 50.18 3.76 50.235 3.77 50.29 3.78 50.345 3.79 50.4 3.8 50.455 3.81 50.51 3.82 50.565 3.83 50.62 3.84 50.675 3.85 50.73 3.86 50.785 3.87 50.84 3.88 50.895 3.89 50.95 3.9 51.005 3.91 51.06 3.92 51.115 3.93 51.17 3.94 51.225 3.95 51.28 3.96 51.335 3.97 51.39 3.98 51.445 3.99 51.5 4 51.555 4.01 51.61 4.02 51.665 4.03 51.72 4.04 51.775 4.05 51.83 4.06 51.885 4.07 51.94 4.08 51.995 4.09 52.05 4.1 52.105 4.11 52.16 4.12 52.215 4.13 52.27 4.14 52.325 4.15 52.38 4.16 52.435 4.17 52.49 4.18 52.545 4.19 52.6 4.2 52.655 4.21 52.71 4.22 52.765 4.23 52.82 4.24 52.875 4.25 52.93 4.26 52.985 4.27 53.04 4.28 53.095 4.29 53.15 4.3 53.205 4.31 53.26 4.32 53.315 4.33 53.37 4.34 53.425 4.35 53.48 4.36 53.535 4.37 53.59 4.38 53.645 4.39 53.7 4.4 53.755 4.41 53.81 4.42 53.865 4.43 53.92 4.44 53.975 4.45 54.03 4.46 54.085 4.47 54.14 4.48 54.195 4.49 54.25 4.5 54.305 4.51 54.36 4.52 54.415 4.53 54.47 4.54 54.525 4.55 54.58 4.56 54.635 4.57 54.69 4.58 54.745 4.59 54.8 4.6 54.855 4.61 54.91 4.62 54.965 4.63 55.02 4.64 55.075 4.65 55.13 4.66 55.185 4.67 55.24 4.68 55.295 4.69 55.35 4.7 55.405 4.71 55.46 4.72 55.515 4.73 55.57 4.74 55.625 4.75 55.68 4.76 55.735 4.77 55.79 4.78 55.845 4.79 55.9 4.8 55.955 4.81 56.01 4.82 56.065 4.83 56.12 4.84 56.175 4.85 56.23 4.86 56.285 4.87 56.34 4.88 56.395 4.89 56.45 4.9 56.505 4.91 56.56 4.92 56.615 4.93 56.67 4.94 56.725 4.95 56.78 4.96 56.835 4.97 56.89 4.98 56.945 4.99 57 5
Im looking for the approximate valie of 33.33 in the second column
I really appreciate your responces.
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>
first column increases by .055 second by .01
so use lookup to find 33.295 then see how far 33.33 is in the range 33.295 to 33.35
and then go the same distance in .69 to .70
Threads
1,216,192
Messages
6,129,443
Members
449,509
Latest member
ajbooisen