How to Use Vlookup and the True and False Value Correctly in Excel 2007 and Excel 2010 With Examples

Updated on October 20, 2016

Introduction

Hi and welcome to my second hub on the VLOOKUP function in Excel. In today’s hub, I am looking at how the TRUE and FALSE values work within the VLOOKUP function and how to avoid getting an incorrect value returned when using the TRUE flag in particular. VLOOKUP allows you to find a value within a range of cells elsewhere in your workbook and return information about it. To use an example:

I have a music store and use Excel to store my customer orders. I want Excel to automatically tell me the cost of a customers order. So for example, a customer orders Green by R.E.M. on CD. I enter the order into my spreadsheet and Excel checks my catalogue and returns with the cost of the album.

I have a hub that covers VLOOKUP in further detail using my music store example. This hub can be found here.

Example of how to use VLOOKUP to return information about an item you are interested in (in this example an item's cost) using Excel 2007 and Excel 2010.
Example of how to use VLOOKUP to return information about an item you are interested in (in this example an item's cost) using Excel 2007 and Excel 2010. | Source

What the TRUE and FALSE values do within the VLOOKUP function in Excel 2007 and Excel 2010

The TRUE and FALSE values are together known as the Range_lookup value within the VLOOKUP function. They allow you to tell Excel if it should be looking for an exact match or an approximate match compared to the contents of the cell you are interested in. The way they work is summarised in the table below

Value
Excel looks for
Excel returns
TRUE
An exact, or approximate match
If Excel is unable to find an exact match, the next value that is less than the value you are interested in is returned
FALSE
An exact match
If Excel is unable to find a match, it returns #N/A

As you can see from the above table, Excel will return an approximate value if you choose TRUE and the exact value is not within the range you specify.

An example of how using the TRUE value can return incorrect answers when using VLOOKUP in Excel 2007 and Excel 2010

To illustrate the problems that can occur when the TRUE value is not used correctly, we will work through an example.

In this example, I have a class of students entering University. I have all of their University Entrance (UE) results and want to be able to type in their names and have Excel fetch their UE results automatically.

Data that will be used to illustrate how the TRUE value in VLOOKUP can return incorrect results in Excel 2007 and Excel 2010.
Data that will be used to illustrate how the TRUE value in VLOOKUP can return incorrect results in Excel 2007 and Excel 2010. | Source

In the figure above, you can see the list of the students and their grades in the cells E43:F56 in my workbook. Excel will check this range for a name that matches the name in the cell I specify (A42 for the first student Matthew). To achieve this, I will use the following formula

=VLOOKUP(A42,$E$43:$F$56,2,TRUE)

The formula is made up of the following components:

  1. The target cell you are asking Excel to compare A42
  2. The range of cells you are asking Excel to compare the target against $E$43:$F$56
  3. The number of columns in the range 2
  4. Whether Excel is looking for an approximate or exact match TRUE

Excel comes back with some rather curious results as shown below

Example of VLOOKUP returning incorrect results with the TRUE value in Excel 2007 and Excel 2010.
Example of VLOOKUP returning incorrect results with the TRUE value in Excel 2007 and Excel 2010. | Source

If you look at the results, Excel has indeed thrown up some very strange results. I have coloured the results as green if correct and red if incorrect.

Example of VLOOKUP returning incorrect (coloured red) results with the TRUE value in Excel 2007 and Excel 2010.
Example of VLOOKUP returning incorrect (coloured red) results with the TRUE value in Excel 2007 and Excel 2010. | Source

Of those incorrect results, Joe is not even in the list of students, although he was given a score of 325! Carl and Carla both got #N/A rather than a number and Excel got 3/16 correct.

How to use VLOOKUP with TRUE and obtain correct results in Excel 2007 and Excel 2010

The secret to using VLOOKUP with TRUE and having it return 100% correct results lies with the reference date you ask Excel to compare the cell you are interested with.

If you sort the data then it will return correct results. To do this:

  • Select the first row of the reference data (in my example above, it is E43: E55)
  • Click on the Sort A to Z button on the Data tab in the Sort & Filter group
  • Select Expand the selection so that it sorts F43:F55 so that the student’s names match their results once sorted
  • Click OK

The VLOOKUP results will update automatically. I have again coloured the cells where Excel returns the correct result as green and incorrect results as red in the figure below.

VLOOKUP results with TRUE after the data has been sorted in Excel 2007 and Excel 2010.
VLOOKUP results with TRUE after the data has been sorted in Excel 2007 and Excel 2010. | Source

This time, Excel got all the results right except that it gave the mysterious Joe a result of 367 despite not existing. The eagle eyed amongst you will notice that he was given Joanne’s result. This in fact is not an incorrect result at all as we were using TRUE, which returns the exact result OR the nearest result it can find (the next largest when working with numbers). This illustrates the importance of using TRUE and FALSE correctly.

Using VLOOKUP with the FALSE value in Excel 2007 and Excel 2010

The FALSE value returns either the exact match or an error if it is unable to find an exact match so whether the reference data is sorted or not, it comes back with 15 correct answers out of 16 and a #N/A for Joe as Joe does not exist in the reference data.

VLOOKUP results using the FALSE value to return exact matches in Excel 2007 and Excel 2010.
VLOOKUP results using the FALSE value to return exact matches in Excel 2007 and Excel 2010. | Source

Other instances where VLOOKUP can return incorrect results in Excel 2007 and Excel 2010

There are two other instances where VLOOKUP can fail to return accurate results.

The first is especially important if you are importing or copying your reference data (the range of cells Excel will compare the target cell with).

  • If your reference data has leading or trailing spaces or non-printable characters this can lead to unpredictable results. Trailing spaces are particularly difficult to spot in your workbook, so use the CLEAN function to remove any unprintable characters or TRIM to delete any spaces added to the cell (except for any single spaces between words).

I have a hub that covers in depth using both the TRIM and CLEAN functions which can be found here.

  • The second instance is when you have dates or numbers stored as text. This is also likely to result in Excel throwing back unexpected results.

Conclusion

The VLOOKUP function is an immensely powerful and very useful function in Excel 2007 and Excel 2010. In today’s hub

  • We have looked at how the TRUE value looks at your reference data in your workbook and returns either the exact result, or an approximation.
  • We then investigated how using the TRUE value can lead to Excel returning incorrect results and how to avoid this by sorting the reference data.
  • Finally, we examined other instances where VLOOKUP can return incorrect data when the data has trailing or leading spaces or unprintable characters and how to fix this using the TRIM and CLEAN functions as well as ensuring the numbers and dates are not stored as text.

When using the TRUE value with VLOOKUP, it is essential to sort the data to ensure that Excel returns accurate data. The FALSE value does not need the data to be sorted as it is looking only for exact matches. Whichever value you choose to use, it is important to understand how both work and how to ensure that Excel returns accurate answers for you.

I do hope that you have found this hub useful and informative and that you are now able to use VLOOKUP so that it always returns the correct result. Many thanks for reading and feel free to leave any comments you may have below.

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile imageAUTHOR

        Robbie C Wilson 

        3 years ago

        Hi Doris,

        Thanks for your kind comment, I am glad that you found my Hub useful and informative :)

      • doris and me profile image

        Mohammad Tanvir Ibne Amin 

        3 years ago from Dhaka

        Its very difficult to work with MSExcel. But the way you described in the article is very useful. Great hub.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)