Education please on V or H Lookup False?

[Carl B]

What is the purpose for using a false value with the lookups?
This message was edited by Carl B on 2002-03-01 23:02

 

[Derek]

Hi

VLOOKUP & HLOOKUP formulas can give you misleading results if the target you are searching for is not in your list. For example Vlookup searches from the top of your list downwards, if it can't find your target it may give you the result of the nearest it can get to it - and you will be unaware this is incorrect. Also the list must be in sequential order, alphabetical and numerical or lookup will fail and give you a false result.
However, if you add "false" at the end of the list of arguements these formulas will return an error result if they cannot find an exact match. This is better than false information and allows you to devise strategies to deal with it.
Hope this helps
Regards
Derek

 

[Aladin Akyurek]

On 2002-03-01 22:59, Carl B wrote:
What is the purpose for using a false value with the lookups?
This message was edited by Carl B on 2002-03-01 23:02

Carl,

This from the Help file on the VLOOKUP worksheet function is the key to when to use or not to use FALSE (or 0) in VLOOKUP formulas:

"If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted."

Aladin