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>
Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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,215,430
Messages
6,124,849
Members
449,194
Latest member
HellScout