"TRUE" for an Approximate result in a formula

[StuartM1]

Hi all,
When entering a formula, we often need to choose between entering TRUE or FALSE.
(I would always enter a zero but it seems FALSE is more reliable.)

Entering TRUE would be for an APPROXIMATE result, but I tested this and need to ask – What rules are used to determine approximate??

The visual below shows a result for TRUE that does not seem to be the most APPROXIMATE result.

This is the Table
[TABLE="width: 729"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 731"]
<tbody>[TR]
[TD][TABLE="width: 731"]
<tbody>[TR]
[TD][TABLE="width: 731"]
<tbody>[TR]
[TD]Expiration >
[/TD]
[TD]Expiration <
[/TD]
[TD]Multiplier
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/31/2000
[/TD]
[TD]6/30/2012
[/TD]
[TD]0.8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2013
[/TD]
[TD]8/5/2017
[/TD]
[TD]0.6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date and Calculation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expiration
[/TD]
[TD]Result
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/2/2013
[/TD]
[TD]0.8
[/TD]
[TD][/TD]
[TD]Cell B10 shows ".8" which contains = VLOOKUP(A10,A4:C5,3,TRUE)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shouldn't the result be .6 ?
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any ideas how it arrives at an approximate?


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks!!!!
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Entering TRUE would be for an APPROXIMATE result, but I tested this and need to ask – What rules are used to determine approximate??

Hi

Always check the help.

From the help:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Don't forget also the note in the help:

Important: If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

 

[Joyner]

Hi all,
When entering a formula, we often need to choose between entering TRUE or FALSE.
(I would always enter a zero but it seems FALSE is more reliable.)

Entering TRUE would be for an APPROXIMATE result, but I tested this and need to ask – What rules are used to determine approximate??

The visual below shows a result for TRUE that does not seem to be the most APPROXIMATE result.

This is the Table
[TABLE="width: 729"]
<tbody>[TR]
[TD]
[TABLE="width: 731"]
<tbody>[TR]
[TD]
[TABLE="width: 731"]
<tbody>[TR]
[TD]
[TABLE="width: 731"]
<tbody>[TR]
[TD]Expiration >
[/TD]
[TD]Expiration <
[/TD]
[TD]Multiplier
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/31/2000
[/TD]
[TD]6/30/2012
[/TD]
[TD]0.8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2013
[/TD]
[TD]8/5/2017
[/TD]
[TD]0.6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date and Calculation
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expiration
[/TD]
[TD]Result
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/2/2013
[/TD]
[TD]0.8
[/TD]
[TD][/TD]
[TD]Cell B10 shows ".8" which contains = VLOOKUP(A10,A4:C5,3,TRUE)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shouldn't the result be .6 ?

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any ideas how it arrives at an approximate?

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks!!!![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If you want to see a good explanation by Aladin:

https://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html

 

[StuartM1]

Ok thanks very much PGC.
I played with it some more and indeed sorting with oldest above newest (what used to be Ascending) does work as you and Help section say. Thanks!!!

 

[StuartM1]

Thanks Joyner!

 

[pgc01]

I'm glad we could help.

 

[MARK858]

@StuartM1, just out of interest where have you found the use of False more reliable to using 0?

(I would always enter a zero but it seems FALSE is more reliable.)