Vlookup TRUE or FALSE

ShaunD30

Board Regular
Joined
Jun 19, 2008
Messages
172
Just a silly question..But I do Vlookups all the time and every time, I am using FALSE to get an exact match.

I have read a lot of posts on this message board dealing with Vlookup solutions, and they are all using exact FALSE.

I tried playing around with using TRUE ..but I don't think I understand what using "TRUE" actually does..and how it effects the formula.

Can someone help me understand this rarely used method, and offer an example of when to use TRUE rather than FALSE.

Shaun
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
True gives an approximate match, so if the value you're looking up is not found in the list it uses the nearest it can find without going over.

For example
=VLOOKUP(17,A1:A9,1,TRUE)
<TABLE style="WIDTH: 40pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=66 border=0><COLGROUP><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=66><TBODY><TR style="HEIGHT: 15pt" height=25><TD id=td_post_1873510 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=66 height=25>5</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=25>10</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=25>15</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=25>20</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=25>25</TD></TR><TR style="HEIGHT: 15.75pt" height=26><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" align=right height=26>30</TD></TR>



<TR style="HEIGHT: 15pt" height=25><TD id=td_post_1873510 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=25>35</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=25>40</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=25>45</TD></TR>
</TBODY></TABLE>
would give the value of 15

looking up any value higher than the range would return an error, and if your lookup range is not in descending order it can give the wrong result.

Hope this helps
 
Upvote 0
Using TRUE is the same as omitting FALSE and the highest value, less than that required, will be returned.

Kelbo
 
Upvote 0
If TRUE gives an approximate match if there is no match..shouldn't it still give an exact match if there is one?
 
Upvote 0
And is it used mainly for numbers..or would it be used in cases where you want to lookup text values that might be almost exact..

example : Ashlee vs Ashley
 
Upvote 0
AHA! I got it. The reason I was so confused was because I never had my table array in ascending order. As soon as I did that...the light came on and my formula worked perfectly. Now I get it :)

Thanks

Shaun
 
Upvote 0
Can be used for text as well, and it is less than or equal, so an exact match would still be found, sorry, didn't explain that part properly before.

Order is still critical regardless of entries though, if it finds a higher value before an exact match it will still take the value before the higher one that it found.

Ashlee vs Ashley

searching for Ashley would find Ashlee, but not the other way around.<!-- / message --><!-- sig -->
 
Upvote 0
This is great. So if I am trying to put a staffing model together lets say 1 x-worker for every 5 y's sold:

Workers (x): Goods (y):
1 5
2 10
3 15
4 20
5 25

I am trying to use this tool that will pull in the number of workers needed. I had created EXTREMELY LONG If formula's which worked, but this seems easier. In other words, if the # of good sold was 12 lets say, it should still pull in 2. I was under impression (from using the false vlookup so much) that it wouldn't pull. If I use a TRue v-lookup it should then pull 2 over?

I have been scared of using the TRUE formulas in the past (when using words not numbers) because it will consistently pull incorrect names that screws stuff up for me. I had one bad experience and stopped using.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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