Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup not giving correct output
Pete_UK wrote:
You need to amend your formula like so: =VLOOKUP(A1;$A$1:$B$4;1;0) The zero at the end (or it could be FALSE) tells XL to look for an exact match. If it is missing, then the table being looked up need to be sorted, and your's isn't. Hope this helps. Sorry for the late reply. It does. thanks. I thought that leaving it out would automaticaly make it FALSE or 0. houghi -- Quote correct (NL) http://www.briachons.org/art/quote/ Zitiere richtig (DE) http://www.afaik.de/usenet/faq/zitieren Quote correctly (EN) http://www.netmeister.org/news/learn2quote.html |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup not giving correct output
Thanks for feeding back - glad it worked for you.
If you omit the 4th parameter then it defaults to TRUE, meaning that the source data is expected to be sorted. (Behind the scenes, XL can carry out a faster binary search for an item if the list is sorted, but if it isn't then it has to carry a sequential search which can be a lot slower. I've never understood, though, why there isn't an option to say that you want an exact search AND the data is sorted, so that a binary search can be carried out internally) Pete On Jul 3, 3:21*pm, houghi wrote: Pete_UK wrote: You need to amend your formula like so: =VLOOKUP(A1;$A$1:$B$4;1;0) The zero at the end (or it could be FALSE) tells XL to look for an exact match. If it is missing, then the table being looked up need to be sorted, and your's isn't. Hope this helps. Sorry for the late reply. It does. thanks. I thought that leaving it out would automaticaly make it FALSE or 0. houghi -- Quote correct * (NL)http://www.briachons.org/art/quote/ Zitiere richtig (DE)http://www.afaik.de/usenet/faq/zitieren Quote correctly (EN)http://www.netmeister.org/news/learn2quote.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup not giving correct output
Pete_UK wrote:
Thanks for feeding back - glad it worked for you. If you omit the 4th parameter then it defaults to TRUE, Strange. I would think things to be FALSE more logical, especialy when FALSE == 0 To me 0 is always the default. :-/ houghi -- Quote correct (NL) http://www.briachons.org/art/quote/ Zitiere richtig (DE) http://www.afaik.de/usenet/faq/zitieren Quote correctly (EN) http://www.netmeister.org/news/learn2quote.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup not giving correct output
I think it goes back to Lotus 123 days.
Their =vlookup() doesn't have a 4th parameter (IIRC) and in excel, the 123 version would use True (or not specified). So if MS wanted to be consistent with 123, then they have to make it work like 123--even if MS did enhance =vlookup() with the 4th parameter. houghi wrote: Pete_UK wrote: Thanks for feeding back - glad it worked for you. If you omit the 4th parameter then it defaults to TRUE, Strange. I would think things to be FALSE more logical, especialy when FALSE == 0 To me 0 is always the default. :-/ houghi -- Quote correct (NL) http://www.briachons.org/art/quote/ Zitiere richtig (DE) http://www.afaik.de/usenet/faq/zitieren Quote correctly (EN) http://www.netmeister.org/news/learn2quote.html -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup not giving correct output | Excel Discussion (Misc queries) | |||
Vlookup giving #N/A | Excel Worksheet Functions | |||
Conditional Formatting not giving correct answer... | Excel Worksheet Functions | |||
VLookup in VBA giving error message | Excel Discussion (Misc queries) | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions |