VlookUp function's fourth argument
- Thread starter Dheeraj
- Start date
NARAYANK991
Excel Ninja
Hi Dheeraj ,
Are you sure it is the VLOOKUP function ?
The syntax for the VLOOKUP function is :
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
where the fourth argument can only take on the value TRUE or FALSE.
Narayan
Are you sure it is the VLOOKUP function ?
The syntax for the VLOOKUP function is :
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
where the fourth argument can only take on the value TRUE or FALSE.
Narayan
Hi,
The fourth parameter is either True or False, but it can also take numeric 1 or 0, where 1 stands for True and 0 represent false. You are specifying whether the table is sorted or not.
When to use True and when False is used:
When False, it means the data in table is not sorted and it will fetch exact match of what you are searching and if not found it will return #N/A.
When True, you mean the data is sorted and it will fetch exact match of what you are searching and if not found it will return nearest value.
pls visit http://chandoo.org/excel-formulas/vlookup.html
or
http://www.contextures.com/xlFunctions02.html
Regards,
The fourth parameter is either True or False, but it can also take numeric 1 or 0, where 1 stands for True and 0 represent false. You are specifying whether the table is sorted or not.
When to use True and when False is used:
When False, it means the data in table is not sorted and it will fetch exact match of what you are searching and if not found it will return #N/A.
When True, you mean the data is sorted and it will fetch exact match of what you are searching and if not found it will return nearest value.
pls visit http://chandoo.org/excel-formulas/vlookup.html
or
http://www.contextures.com/xlFunctions02.html
Regards,
Nice explanation, prasaddn.
To elaborate slightly: when dealing with a expected Boolean (true/false) input, XL will accept any numerical value. However, only a 0 is treated as false. Additionally, leaving a blank, is equivalent to false. Thus, all of these evaluate the same:
=VLOOKUP("Chandoo",A:C,3,FALSE)
=VLOOKUP("Chandoo",A:C,3,0)
=VLOOKUP("Chandoo",A:C,3,)
Why does it matter? It doesn't really. It's only helpful when you're either a) having the 4th argument being determined by some other function which might return a numerical value rather than Boolean, or b) you wan't to save a few keystrokes.
To elaborate slightly: when dealing with a expected Boolean (true/false) input, XL will accept any numerical value. However, only a 0 is treated as false. Additionally, leaving a blank, is equivalent to false. Thus, all of these evaluate the same:
=VLOOKUP("Chandoo",A:C,3,FALSE)
=VLOOKUP("Chandoo",A:C,3,0)
=VLOOKUP("Chandoo",A:C,3,)
Why does it matter? It doesn't really. It's only helpful when you're either a) having the 4th argument being determined by some other function which might return a numerical value rather than Boolean, or b) you wan't to save a few keystrokes.
Hi All,
Sorry for the late reply as I could not visit Chandoo.com in past few days.
Thank you so much for your help but actually I am very well aware with the Vlookup function's syntex and know that last argument can take only two values (Tru/False i.e. 1/0). However, confusion comes when I put any other number than 0/1 because it still gives output not any error.
Hope this will enable you to understand my point or reason for my confusion.
Thanks
Sorry for the late reply as I could not visit Chandoo.com in past few days.
Thank you so much for your help but actually I am very well aware with the Vlookup function's syntex and know that last argument can take only two values (Tru/False i.e. 1/0). However, confusion comes when I put any other number than 0/1 because it still gives output not any error.
Hope this will enable you to understand my point or reason for my confusion.
Thanks
Hi Dheeraj,
As Luke explained, 4th argument is to specify True or False, and you can specify False in 3 ways - a) FALSE, b) 0 and c) <blank>.
Having said that, anything other than above 3, represents a True attribute. Hence, vlookup does not throw you any error.
Hope this resolves your confusion
Regards,
Prasad
As Luke explained, 4th argument is to specify True or False, and you can specify False in 3 ways - a) FALSE, b) 0 and c) <blank>.
Having said that, anything other than above 3, represents a True attribute. Hence, vlookup does not throw you any error.
Hope this resolves your confusion
Regards,
Prasad