WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » VLOOKUP True

VLOOKUP True

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

VLOOKUP with TRUE

99.99% of the time, everybody uses FALSE as the range lookup criteria because 99.99% of the time, we need an exact match from the table array. Even in the training sessions, your trainers must have explained only FALSE criteria and would have said not to worry about the TRUE criteria. Probably because of the not arising scenario, we have not used TRUE criteria, but in this article, we will show you how to use TRUE criteria in VLOOKUP with different scenarios.

VLOOKUP Closest Matching Result by Using TRUE Option

First, take a look at the syntax of the VLOOKUP formula.

Vlookup Formula

In the above syntax, all the arguments of the VLOOKUP function are mandatory, but the last argument [Range Lookup] is optional. For this argument, we can supply two parameters, i.e., either TRUE (1) or FALSE (0).

If you supply TRUE (1) then it will find the approximate match, and if you supply FALSE (0), it will find the exact match.

You can download this VLOOKUP True Excel Template here – VLOOKUP True Excel Template

Now take a look at the below data set in excel.

Vlookup True (Given Data)

Above, we have numbers from 3 to 20, and on the right-hand side, we have a lookup value number as 14, but this number does not exist in the main numbers table.

Now first apply the VLOOKUP function with FALSE as the range lookup criteria to find the exact match.

Vlookup True (VLOOKUP Formula)

Now hit the enter key to get the result of the formula.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Vlookup True (VLOOKUP Result)

We have got an error value of not available #N/A as a result.

Now change the range lookup criteria from FALSE (0) to TRUE (1).

Vlookup True (VLOOKUP True Result)

This time we have got the result as 10. You must be wondering about the number 14, which doesn’t exist in the table array. How did this parameter has returned 10 as a result?

Let me explain to you the result for you.

We have set the range lookup argument is TRUE, so it finds the closest match for the lookup value provided (14).

How this works is “our lookup value is 14, and the VLOOKUP starts to search from top to bottom, when the lookup value is lesser than the value in the table it will stop at that point in time return the respective result”.

For example, in our data, 14 is greater than 10 and lesser than 15, so the moment VLOOKUP finds the value 15, it will go back and return the previous smaller value, i.e., 10.

Vlookup True (Matching Result)

To test this, change the value from 10 to 15 and see the magic.

Vlookup True (New result)

Because we have changed the current lesser value more than the lookup, it has returned the previous lesser value, i.e., 8.

VLOOKUP TRUE as Alternative to IF Condition

IF is the important function in excel, and for all the criteria based calculations, we use IF statements. For example, look at the below data.

Vlookup True Example 2

We have two tables here, “Sales Table” and “Incentive %” Table.” For “Sales Table,” we need to arrive at incentive % based on the revenue generated by each employee. To calculate incentive %, we have the below criteria.

  • If the revenue is >50000, then the incentive % will be 10%.
  • If the revenue is >40000, then incentive % will be 8%.
  • If the revenue is >20000, then incentive % will be 6%.
  • If the revenue is <20000, then incentive % will be 5%.

So we have four criteria to satisfy. In these cases, we use typical IF conditions to arrive at incentive %, but now see how we can use VLOOKUP arrive at incentive %.

Apply the VLOOKUP formula with TRUE as the criteria.

Vlookup True Example 2 (Incentive Percent)

There you go. We have our incentive % against the revenue generated by each employee. Let me explain to you how this works.

First, look at the Incentive % table.

Vlookup True (Incentive Table)

  • This says between 0 to 20000 incentive % is 5%.
  • Between 20001 to 40000, incentive % is 6%.
  • Between 40001 to 50000, incentive % is 8%.
  • Anything above 50000 incentive % is 10%.

Since we have provided TRUE as the range lookup argument, it will return the approximate match.

Look at the first case, in this revenue is 35961, this is less than the incentive table value of 40000, and lesser value than 40000 in the table is 20000, and for this incentive, % is 6%.

Like this, the TRUE function works and says goodbye to complex IF conditions.

Things to Remember

  • TRUE finds the approximate match.
  • TRUE is also represented by 1.
  • In the case of numerical scenario’s it always finds the less than or equal to the lookup value in the table array.
  • If the lookup value is lesser than all the values in the lookup table, then it will return an error like #N/A.

Recommended Articles

This has been a guide to VLOOKUP with True. Here we discuss how to use true criteria with VLOOKUP in different scenarios along with practical examples and downloadable excel templates. You may learn more about excel from the following articles –

  • VLookup in VBA
  • VLOOKUP with Two Criteria
  • VLOOKUP with Match
  • VLOOKUP in Pivot Table
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VLOOKUP True Excel Template

Coursera IPO Financial Model & Valuation Free Download