Hi, I am attempting to use the VLOOKUP function, but can't see where the error is in the following. I want whatever number is in N54 in worksheet 1 to be matched to the exact same number in column A of worksheet titled '7.SHAPIRO-WILK SIGNIFICANCE' and then the corresponding value across in column C on this worksheet inserted into the original cell in worksheet 1. =VLOOKUP(N54,'7.SHAPIRO-WILK SIGNIFICANCE'!A3:C50,'7.SHAPIRO-WILK SIGNIFICANCE'!C3:C50,FALSE) Appreciate any suggestions. Thanks

Look at the Help files for VLOOKUP and compare the construction of your formula to both the text description and the examples. Do it argument by argument. I think you'll see a major difference.

Thanks, quite an obvious error on my part - I need to be more carful !. So the correct formula is: =VLOOKUP(N54,'7.SHAPIRO-WILK SIGNIFICANCE'!A3:C50,3,FALSE) Now one final quick one is that if I wanted to look up the corresponding value to N54 on the worksheet 7, and then when the relevant value is identified in Column 1 (A), the function reads across the row and picks out the value closest to the value in Cell A55 on Worksheet 1. Is that do-able without delivng into VB or macros ? Thanks Again

1 - Now that you've got the formula right, I'll toss in another tip: 0 is equivalent to FALSE, but takes 4 less keystrokes. 2 - re: Is that do-able without delving into VB or macros ? It's VBA (Visual Basic for Applications) and it's used to write macros. It's not one or the other. 3 - re: picks out the value closest to the value in Cell A55 on Worksheet 1 What if there is an exact match? Closest without going over or closest without going under? What if there is a tie? e.g. 4 and 6 are equidistant from 5.

ok, points 1. and 2. noted. Regarding point 3. If there is an exact match then it needs to choose that value, otherwise the nearest. If there is tie, which is possible but not as likely as you suggest given the values are at three decimal places, then i would say the lower of the values should be chosen as this would make for a more conservative assessment, which suits the nature of what I am attempting to put together.

That's going to involve VBA in the form of a UDF (User Definded Function). A User Defined Function is a function that can be used in Excel but which calls some VBA code to determine the final value of the function. If you paste the following code into a regular VBA module, you can use the UDF NearestNumber() in your worksheet by entering something like this in a cell: =NearestNumber(A1:A20,C1) This should find the value in A1:A20 that is closest to the value in C1. I won't take credit for writing this, but I will take credit for finding it and incorporating it into the formula at the end of this post. ;-) Function NearestNumber(varValue As Variant, rngRange As Range) As Variant Dim sngDistance As Single Dim varResult As Variant Dim c As Range sngDistance = 0 For Each c In rngRange If c = rngRange.Cells(1, 1) Or Abs(c.Value - varValue) < sngDistance Then sngDistance = Abs(c.Value - varValue) varResult = c.Value End If Next NearestNumber = varResult End Function So without thinking about this too deeply, here is how I would solve your overall issue: 1 - I would add a column to the end of the table where I have my data. 2 - I would use the NearestNumber UDF in that column so that that column would always contain the number from each row that is closest to the value in A55. Since it's a volitile function, the result will change as you change the number in A55 or in a given row, just like any Excel function. 3 - I would use the VLOOKUP function to return the value in that column. In other words, the table_array in the VLOOKUP function would include that column, and that column would be col_index_num. e.g. N54 contains Car A55 contains 5.25 D1 contains =NearestNumber($A$55,B1:C1) D2 contains =NearestNumber($A$55,B2:C2) Etc. =VLOOKUP(N54,A1:D4,4,0) will return 3 since that is the closest number to 5.25 in the row that contains Car. A B C D 1 Bike 4 7 4 <- Result of UDF 2 Boat 5 6 5 <- Result of UDF 3 Car 3 8 3 <- Result of UDF 4 Plane 2 6 6 <- Result of UDF

Thanks for that I shall give it a go.