Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLookUp
I have created a VLookUp table that looks at Column A in one workbook,
compares it to another column in the LookUp table and if the values are the same, it returns a value from one of the other columns - Column D - the third column in the LookUp. The actual LookUp works fine and where there are identical matches, it returns the correct value. However, even when the values in the LookUp table and the other workbook do not match, it returns a figure and I don't know why. I don't know where the figure has come from and how Excel has arrived at it? Does anybody have any ideas how this has happened? THank you. Louise |
#2
|
|||
|
|||
Louise,
It is doing a lookup on the value below. If you add ,FALSE at the end (=VLOOKUP(val, table, 2,FALSE), it will do an exact match, and return #N/A if it doesn't match. -- HTH RP (remove nothere from the email address if mailing direct) "Louise" wrote in message ... I have created a VLookUp table that looks at Column A in one workbook, compares it to another column in the LookUp table and if the values are the same, it returns a value from one of the other columns - Column D - the third column in the LookUp. The actual LookUp works fine and where there are identical matches, it returns the correct value. However, even when the values in the LookUp table and the other workbook do not match, it returns a figure and I don't know why. I don't know where the figure has come from and how Excel has arrived at it? Does anybody have any ideas how this has happened? THank you. Louise |
#3
|
|||
|
|||
By default Louise Vlookup gives an approximate match, i.e. if it cant find
exact match it gives what it thinks is nearest. To overcome this, at the end of the Vlookup formula, and within the brackets type ",false" without the inverted commas. I.e. Vlookup(mynumber, Column A, 3,false) Hope that helps Happy Christmas Philip "Louise" wrote in message ... I have created a VLookUp table that looks at Column A in one workbook, compares it to another column in the LookUp table and if the values are the same, it returns a value from one of the other columns - Column D - the third column in the LookUp. The actual LookUp works fine and where there are identical matches, it returns the correct value. However, even when the values in the LookUp table and the other workbook do not match, it returns a figure and I don't know why. I don't know where the figure has come from and how Excel has arrived at it? Does anybody have any ideas how this has happened? THank you. Louise |
#4
|
|||
|
|||
I forgot about that!!! Thank you very much.
HAVE A LOVELY CHRISTMAS Louise. "Philip Reece-Heal" wrote: By default Louise Vlookup gives an approximate match, i.e. if it cant find exact match it gives what it thinks is nearest. To overcome this, at the end of the Vlookup formula, and within the brackets type ",false" without the inverted commas. I.e. Vlookup(mynumber, Column A, 3,false) Hope that helps Happy Christmas Philip "Louise" wrote in message ... I have created a VLookUp table that looks at Column A in one workbook, compares it to another column in the LookUp table and if the values are the same, it returns a value from one of the other columns - Column D - the third column in the LookUp. The actual LookUp works fine and where there are identical matches, it returns the correct value. However, even when the values in the LookUp table and the other workbook do not match, it returns a figure and I don't know why. I don't know where the figure has come from and how Excel has arrived at it? Does anybody have any ideas how this has happened? THank you. Louise |
#5
|
|||
|
|||
VLOOKUP can have a zero or a one at the end - if you have a one it will look
for the closest match so may return values even if there are no matches. If you use a zero it will only return a value if there is an exact match. eg. VLOOKUP(A1,D1:E6,1,0) J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with modifying VLookUp | Excel Discussion (Misc queries) | |||
VLOOKUP help | Excel Worksheet Functions | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions | |||
vlookup. | Excel Worksheet Functions |