Glide Training Logo

September 2015

Excel Vlookup using TRUE

Written by Jane Hames

This guide is aimed at Excel users who can already use the VLookup function and want to learn more about it. This is how to use the Excel Vlookup using TRUE

If you use a VLookup, the chances are you will use FALSE as the Range Lookup finding an exact match for the Lookup Value in the first column of your Table Array. I often get asked when you would use TRUE as your Range Lookup and this is good question as it’s not used half as often as FALSE.

Last week I was able help one of my Excel course delegates to solve a problem she had, and the solution to the problem was to use a Vlookup with TRUE. I wanted to share what we did with my readers and followers as it might give you some good ideas of things you could do with your own work. All data in the guide below is fictional, but based on the real world scenario:

The scenario I was given, is that as shown in the image below, there are a list of date ranges with different exchange rates for different date ranges.Excel Vlookup using TRUE

We wanted to ensure that the correct exchange rate was used to calculate the payment amount in Euros as shown in the image below:Excel Vlookup using TRUEIn order to do this, I used a Vlookup to pick up the correct exchange rate for the specified payment date. This payment date would need to sit in one of the date ranges listed. This is what we used to do this calculation:

LOOKUP_VALUE: The payment date Excel Vlookup using TRUE

TABLE_ARRAY: The table which shows the date ranges listed in ascending order (the Date From in the first column of the table_array is what Excel will use). The exchange rate is in the third column of the table_array.:Excel Vlookup using TRUE

COL_INDEX_NUMBER: 3 This is the column in the table_array that contains the exchange rate we are looking for.

RANGE_LOOKUP: TRUE This ensures that Excel will look at the earliest date in the list that is close to the payment date (our lookup_value).Excel Vlookup using TRUE

This what the formula looks like at the end:Excel Vlookup using TRUE

Note that the payment date is shown as its reference number rather than the date. This is because I have selected SHOW FORMULAS in order to show you the formulae on the sheet. I then copied the formula down so that I have ended up with this end result:Excel Vlookup using TRUE

If you would like help with the Vlookup or other functions in Excel, please get in touch.

-->

Written by Jane Hames

This guide is aimed at Excel users who can already use the VLookup function and want to learn more about it. This is how to use the Excel Vlookup using TRUE

If you use a VLookup, the chances are you will use FALSE as the Range Lookup finding an exact match for the Lookup Value in the first column of your Table Array. I often get asked when you would use TRUE as your Range Lookup and this is good question as it’s not used half as often as FALSE.

Read more on Excel Vlookup using TRUE…


Why choose Glide?

Our Unique Approach

Our Unique Approach

We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you're looking to achieve.

View details »

Our Low Prices

Our Low Prices

We deliver all our on-site training at our customers' premises. Not only does that avoid any travel inconvenience for your team, but also it means we don't have the overheads required to maintain a dedicated training centre - and in turn we are able to pass these savings on to you.

View details »

Our Expertise

Our Expertise

Our trainers have designed and delivered IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

View details »

Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo
Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo