EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VLOOKUP True

By Ashwani JaiswalAshwani Jaiswal

Secondary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Home Excel Excel Resources Lookup & Reference Functions in Excel VLOOKUP True

VLOOKUP True

VLOOKUP True (Table of Contents)

  • VLOOKUP with True
  • How to Use Vlookup True?

VLOOKUP with True

It is obvious that when we apply Vlookup function, we always look for the exact match. When we get the #N/A, then also we assume that the value which we are looking for is not available in the lookup table. But we never have tried using Vlookup with TRUE value match. Vlookup True is used when we don’t get an exact match but with the help of TRUE, we can get an approximate match or near value to the value which we are looking for. We can two criteria in Vlookup functions, which are TRUE and FALSE. FALSE gives us the exact match whereas TRUE gives us the approximate match for the value if not in lookup range.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Use Vlookup True?

To understand the use of Vlookup True, first, we need to understand the syntax of Vlookup. Below we have the syntax of Vlookup;

vlookup names 1-1

Where,

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,275 ratings)
  • Lookup_Value = Cell value whose value we need to find.
  • Table_Array = Range or table from where we need to find the value of lookup_value.
  • Col_Index_Num = sequence number of the column from which we want to get the value.
  • Range_Lookup = 0 (or FALSE) for the exact match and 1 (or TRUE) for the approximate match.

We will be seeing the use of TRUE and FALSE range_lookup value and will see how Vlookup True works in the below examples.

Examples of VLOOKUP True

Lets us discuss the examples of VLOOKUP names.

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

Example #1

In this example, we will see the simple way to apply Vlookup True. For this, we have a list of alphabets as shown below. As we can see the alphabets are in proper sequence. And in separate cells, we will be looking up the values from the list and see the output.

vlookup names 1-2

Now in cell C2, we will put any of the alphabets which we want to lookup from Column A. Let’s consider it D.

vlookup names 1-3

Now in cell D2, insert the vlookup function and select the lookup value and lookup range as needed.

vlookup names 1-4

To get the exact match, we can select FALSE range lookup or we also choose 0 for the exact match as shown below.

vlookup names 1-5

Once we enter, we will be able to see the cell D2 will now has the exact match value from the selected range.

vlookup names 1-6

Now, if we delete the Lookup Cell value D from the list available in column A, then Vlookup will not be able to lookup anything will return #N/A.

vlookup names 1-7

Now, if we change the range lookup value of Vlookup syntax from FALSE to TRUE (or 1), then we will get an approximate match for the lookup value D in cell D2 as shown below.

vlookup names 1-8

Once we exit from the syntax, we will get the value nearer to lookup cell value D in cell D2.

mapped value

As we can see, the obtained value at cell D2 is C. Which is because Vlookup TRUE gives the near about value if the exact match is not available. Here for the D, it returned the approximate match as C because C is the only near value that comes before D.

Example #2

In this example, we will see, how Vlookup True works when we have a larger set of data. Below, we have a table where the name of the different persons along with age and physique type is mentioned from Column A to C. And there is another small table at column E:F where we will be mapping the value from table 1.

Example 2-1

As we can see the for each person, we have age and physique type. Now, let’s try to find out the physique type of any of the age, say 26. Insert the vlookup at cell F2 as shown below.

Example 2-2

Now select the lookup cell E2 and Lookup range from B2 to C8 as shown below.

Example 2-3

Considering FALSE for range lookup, we will get the exact match.

Example 2-4

Now if we change the age from 26 to 53 randomly in cell E2 and again apply the lookup using TRUE range lookup criteria and see what we will get? As of now, when we change the cell E2 value from 26 to 53, we got #N/A, as there is no value in column C available for age 53.

Example 2-5

Now we will apply the Vlookup function using the TRUE.

Example 2-6

Once we press enter or get exited from the Vlookup syntax, then are physique type in cell F2 as GENERAL as shown below.

Example 2-7

The reason behind, getting General in cell F2 is because of TRUE range lookup in vlookup syntax. As we did not have of the age as 53, so Vlookup True returned us the value as General because before 53, we got general physique in cell B4.

If we again change the value, let say 31. Then we will be getting the value which is closer to 31 as per the table. Here we have 30 in cell B2.

Example 2-8

Pros of VLOOKUP True

  • It helps us to get the approximate match if we do not have the exact value available for lookup cell.
  • It is as simple as Vlookup False.
  • Using this function is far better than using different IF condition functions.

Things to Remember

  • It gives an approximate match.
  • We can also use 1 in place of TRUE in VLOOKUP syntax.
  • For the numerical value, It looks up the value less than the value available in the lookup cell.
  • Vlookup False gives the exact match and if the match value is not available then we will be getting #N/A
  • #N/A means that the lookup cell’s value is not found in the entire lookup range.
  • Lookup range could be table or column, but the value which we will get from the column only.

Recommended Articles

This has been a guide to VLOOKUP True. Here we discuss How to Use VLOOKUP True along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Names
  2. VLOOKUP Table Array
  3. VLOOKUP For Text
  4. VLOOKUP with Sum
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download VLOOKUP True Excel Template

EDUCBA

Download VLOOKUP True Excel Template

EDUCBA

डाउनलोड VLOOKUP True Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Excel Training (18 Courses, 9+ Projects) Learn More