Education please on V or H Lookup False?

Carl B

Board Regular
Joined
Feb 15, 2002
Messages
65
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top