Vlookup

Resolved · Low Priority · Version 2007

Thu 15 Sep 2011, 11:48 Delegate Tessa said...

Tessa has attended:
Excel Advanced course

Vlookup

Why is it that if you look for specified data using a vlookup function that if you ask for 'best match' by using TRUE, that it always rounds down and doesn't round according to the mid-point?

Thu 15 Sep 2011, 12:13 Trainer Rodney said...

RE: vlookup

Hello Tessa,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding the use of Vlookup and its value returned when using the TRUE value in the Range_Lookup part of the Syntax.

Unfortunately the Vlookup function does not have different options that you can choose with regard to Range_Lookup. 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.

Please note, 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.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Thu 15 Sep 2011, 12:36 Delegate Tessa said...

RE: vlookup

Yes, but in terms of finding the best match if range _lookup is TRUE, it seems to always round down, even if the best match from our perspective is the number closest to the specified data. Why does it always round down?

Thu 15 Sep 2011, 21:07 Trainer Rodney said...

RE: vlookup

Hello Tessa,

It would be great if you will send me a sample file to show me exactly what you want the Vlookup to do. I will then seek to find a better way to provide you with a solution to your problem.

Email me to rl@stl-training.co.uk

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Tue 20 Sep 2011, 19:17 Delegate Tessa said...

RE: vlookup

Sample file sent with explanation. Tessa

Wed 21 Sep 2011, 22:11 Trainer Rodney said...

RE: vlookup

Hello Tessa,

I have attached the file you sent me, so take a look at the second sheet where I have written a note about what I have done.

Simply put, when using Vlookup, if you sort the lookup column then you can use TRUE or FALSE. If the lookup value cannot be found in the lookup column when using TRUE then the next lowest value is used to produce a result. If you use FALSE and the value cannot be found then you will get an #N/A error.

If you don't sort the lookup column then you cannot use TRUE. Using FALSE with an unsorted column can produce strange results if your data has duplicates in the lookup column.

I hope this resolves your question. If it has, please mark this question as resolved.

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer