Vlookup TRUE or FALSE

Thanks:  0
Likes:  0

1. Vlookup TRUE or FALSE

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

2. Re: Vlookup TRUE or FALSE

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)

 5 10 15 20 25 30 35 40 45

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

3. Re: Vlookup TRUE or FALSE

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

Kelbo

4. Re: Vlookup TRUE or FALSE

If TRUE gives an approximate match if there is no match..shouldn't it still give an exact match if there is one?

5. Re: Vlookup TRUE or FALSE

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

6. Re: Vlookup TRUE or FALSE

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

7. Re: Vlookup TRUE or FALSE

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.

8. Re: Vlookup TRUE or FALSE

It is quite easy to use VLOOKUP(,,,True) for exact matches, and it is orders of magnitude faster.
See
http://www.decisionmodels.com/optspeede.htm

9. Re: Vlookup TRUE or FALSE

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.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•