"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

Expiration > Expiration < Multiplier 12/31/2000 6/30/2012 0.8 7/1/2013 8/5/2017 0.6 Date and Calculation Expiration Result 4/2/2013 0.8 Cell B10 shows ".8" which contains = VLOOKUP(A10,A4:C5,3,TRUE) Shouldn't the result be .6 ? <tbody> </tbody> <tbody> </tbody> Any ideas how it arrives at an approximate? <tbody> </tbody> Thanks!!!!

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[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

Expiration > Expiration < Multiplier 12/31/2000 6/30/2012 0.8 7/1/2013 8/5/2017 0.6 Date and Calculation Expiration Result 4/2/2013 0.8 Cell B10 shows ".8" which contains = VLOOKUP(A10,A4:C5,3,TRUE) Shouldn't the result be .6 ? <tbody> </tbody> <tbody> </tbody> Any ideas how it arrives at an approximate? <tbody> </tbody> Thanks!!!!

<tbody>
</tbody>

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.)