Skip to content
geeksforgeeks
  • Courses
    • DSA to Development
    • Get IBM Certification
    • Newly Launched!
      • Master Django Framework
      • Become AWS Certified
    • For Working Professionals
      • Interview 101: DSA & System Design
      • Data Science Training Program
      • JAVA Backend Development (Live)
      • DevOps Engineering (LIVE)
      • Data Structures & Algorithms in Python
    • For Students
      • Placement Preparation Course
      • Data Science (Live)
      • Data Structure & Algorithm-Self Paced (C++/JAVA)
      • Master Competitive Programming (Live)
      • Full Stack Development with React & Node JS (Live)
    • Full Stack Development
    • Data Science Program
    • All Courses
  • Tutorials
    • Data Structures & Algorithms
    • ML & Data Science
    • Interview Corner
    • Programming Languages
    • Web Development
    • CS Subjects
    • DevOps And Linux
    • School Learning
  • Practice
    • Build your AI Agent
    • GfG 160
    • Problem of the Day
    • Practice Coding Problems
    • GfG SDE Sheet
  • Contests
    • Accenture Hackathon (Ending Soon!)
    • GfG Weekly [Rated Contest]
    • Job-A-Thon Hiring Challenge
    • All Contests and Events
  • Excel Tutorial
  • Excel Formulas
  • Excel Shortcut Keys
  • Data Analysis in Excel
  • Formatting in Excel
  • Excel Workbooks
  • Statistical Functions
  • Data Visualization in Excel
  • Pivot Tables in Excel
  • MS Excel Quiz
  • Excel Interview Questions
  • Advance Excel
Open In App
Next Article:
OFFSET Function in Excel With Examples
Next article icon

Using CHOOSE Function along with VLOOKUP in Excel

Last Updated : 21 Aug, 2021
Comments
Improve
Suggest changes
Like Article
Like
Report

In Excel, managing space is a very important point to consider. Now, let’s suppose you are in a situation where you want to compare multiple columns in the Excel dataset, and you want to derive results, This is not possible using the VLOOKUP function alone. So, to make a function capable of doing something like this, we will introduce a special function known as CHOOSE function. But, if you don’t want to use CHOOSE function, then you have to use a helper column, making the job a little hectic.

So, in this article, we will see how to use VLOOKUP along with CHOOSE function.

First, we will see the syntax of CHOOSE FUNCTION in brief.

CHOOSE FUNCTION:

Syntax:

=CHOOSE(ARRAY DIMENSIONS(INDEX VALUE(S)),VALUE(1),VALUE(2),VALUE(3),.....)

Here,

  1.  Here we will define array dimensions, ie how many columns should the array have.
  2. The next parameter(s) will define what value will go in the subsequent array columns, ie VALUE1 will get filled in the first column, and so on.   

Example:

Here we will use this dataset. Let’s suppose we have people in a sales firm, and they are evaluated for bonuses. Now, we want to know who did sales for BED SHEETS, by using VLOOKUP and CHOOSE function. So, first of all, let’s see what the formula is, and then we will understand it part by part.

=VLOOKUP(B9,CHOOSE({1,2},C2:C6,A2:A6),2,TRUE)

Here the output will be as shown below:

Now, we will see part by part how this formula worked:

  •  B9: This is the targeted value to be compared by VLOOKUP to get the result, ie BED SHEETS.
  • CHOOSE({1,2},C2:C6,A2:A6): This will create an array,having first column values from C2 TO C6,and second column values from A2 TO A6.
  • 2: The value will be fetched from this column number.
  • TRUE: This parameter is for enabling approximate search, ie any keyword found nearly matching with the required word will be given as a result.
  • VLOOKUP(B9, CHOOSE({1,2}, C2:C6, A2:A6),1, TRUE): VLOOKUP will search for the desired keyword from the second column of the array, created by CHOOSE function.

Note: The formula above will use an array, so to execute it use Ctrl+Shift+Enter key combination.

The disadvantage of the above function is that VLOOKUP is not capable of doing a case-sensitive search, so to enhance VLOOKUP you have to make it case sensitive. For reference visit this article.



Next Article
OFFSET Function in Excel With Examples

G
gs2000april
Improve
Article Tags :
  • Excel
  • Microsoft Office
  • News
  • Excel-VBA
  • ExcelGuide

Similar Reads

  • Excel CHOOSE Function with Array
    We often use the CHOOSE function in Excel to pick something from a list. In this article, we'll see how to use CHOOSE with a bunch of things (we call it an array) to do various stuff in Excel. This array can be either many cells we put into the function or many things we get from the function. What
    5 min read
  • How to use CHOOSE Function in Excel
    The CHOOSE function is technically part of Excel’s lookup function and can be incredibly useful. The CHOOSE function returns a value from a list using an index. One of those Excel features, CHOOSE, may not seem helpful alone, but when paired with other functions, it offers a ton of fantastic advanta
    7 min read
  • Statistical Functions in Excel With Examples
    To begin with, statistical function in Excel let's first understand what is statistics and why we need it? So, statistics is a branch of sciences that can give a property to a sample. It deals with collecting, organizing, analyzing, and presenting the data. One of the great mathematicians Karl Pears
    6 min read
  • OFFSET Function in Excel With Examples
    Excel contains many useful formulas and functions that make it more and more useful and at the same time user-friendly. Such a function is the OFFSET() function. In many cases, this function is also used inside another function. This function basically returns a reference of a single cell or a range
    4 min read
  • Using MONTH and EOMONTH functions in Excel
    In this easy-to-follow guide, we'll explore the Excel MONTH and EOMONTH functions. These functions are super handy for working with dates. We'll show you how to use them to figure out which month a date belongs to, get the first and last day of a month, and more. Whether you're new to Excel or an ex
    12 min read
  • Excel ROWS and COLUMNS Functions with Examples
    In Microsoft Excel, where precision and efficiency reign supreme, mastering the art of maneuvering through cells and worksheets is indispensable. Two indispensable components in your Excel toolbox are the ROW and COLUMN functions, each meticulously crafted to execute discrete functions that can subs
    7 min read
  • Excel VLOOKUP Function - Excel Guide for Beginners
    How to do VLOOKUP in Excel - Quick StepsPrepare Your DataEnter the VLOOKUP Formula >> Press EnterUse a Cell Reference for FlexibilityCopy the Formula for Multiple RowsAdding clickable links to your document is a simple yet powerful way to connect readers to external websites, email addresses,
    15 min read
  • How To Use MATCH Function in Excel (With Examples)
    Finding the right data in large spreadsheets can often feel like searching for a needle in a haystack. This is where the MATCH function in Excel proves invaluable. The MATCH function helps you locate the position of a specific value within a row or column, making it a cornerstone of efficient data m
    6 min read
  • Function and Sub in Excel VBA
    In Visual Basic, the functions and sub-procedures play similar roles but have different or unique characteristics. However, both perform a programmed task. They utilize a set or group of commands to deliver the required results. The key difference between the sub and the functions is that a sub-proc
    4 min read
  • VBA Date and Time Functions in Excel
    Date and Time Functions are the inbuilt functions that give us the opportunity to see the date or time according to the user's need. Suppose a user needs to see the month or the day or the year then it can be easily seen by different date functions. Similarly, for the time function, also we can mani
    5 min read
geeksforgeeks-footer-logo
Corporate & Communications Address:
A-143, 7th Floor, Sovereign Corporate Tower, Sector- 136, Noida, Uttar Pradesh (201305)
Registered Address:
K 061, Tower K, Gulshan Vivante Apartment, Sector 137, Noida, Gautam Buddh Nagar, Uttar Pradesh, 201305
GFG App on Play Store GFG App on App Store
Advertise with us
  • Company
  • About Us
  • Legal
  • Privacy Policy
  • In Media
  • Contact Us
  • Advertise with us
  • GFG Corporate Solution
  • Placement Training Program
  • Languages
  • Python
  • Java
  • C++
  • PHP
  • GoLang
  • SQL
  • R Language
  • Android Tutorial
  • Tutorials Archive
  • DSA
  • Data Structures
  • Algorithms
  • DSA for Beginners
  • Basic DSA Problems
  • DSA Roadmap
  • Top 100 DSA Interview Problems
  • DSA Roadmap by Sandeep Jain
  • All Cheat Sheets
  • Data Science & ML
  • Data Science With Python
  • Data Science For Beginner
  • Machine Learning
  • ML Maths
  • Data Visualisation
  • Pandas
  • NumPy
  • NLP
  • Deep Learning
  • Web Technologies
  • HTML
  • CSS
  • JavaScript
  • TypeScript
  • ReactJS
  • NextJS
  • Bootstrap
  • Web Design
  • Python Tutorial
  • Python Programming Examples
  • Python Projects
  • Python Tkinter
  • Python Web Scraping
  • OpenCV Tutorial
  • Python Interview Question
  • Django
  • Computer Science
  • Operating Systems
  • Computer Network
  • Database Management System
  • Software Engineering
  • Digital Logic Design
  • Engineering Maths
  • Software Development
  • Software Testing
  • DevOps
  • Git
  • Linux
  • AWS
  • Docker
  • Kubernetes
  • Azure
  • GCP
  • DevOps Roadmap
  • System Design
  • High Level Design
  • Low Level Design
  • UML Diagrams
  • Interview Guide
  • Design Patterns
  • OOAD
  • System Design Bootcamp
  • Interview Questions
  • Inteview Preparation
  • Competitive Programming
  • Top DS or Algo for CP
  • Company-Wise Recruitment Process
  • Company-Wise Preparation
  • Aptitude Preparation
  • Puzzles
  • School Subjects
  • Mathematics
  • Physics
  • Chemistry
  • Biology
  • Social Science
  • English Grammar
  • Commerce
  • World GK
  • GeeksforGeeks Videos
  • DSA
  • Python
  • Java
  • C++
  • Web Development
  • Data Science
  • CS Subjects
@GeeksforGeeks, Sanchhaya Education Private Limited, All rights reserved
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox
Improvement
Suggest Changes
Help us improve. Share your suggestions to enhance the article. Contribute your expertise and make a difference in the GeeksforGeeks portal.
geeksforgeeks-suggest-icon
Create Improvement
Enhance the article with your expertise. Contribute to the GeeksforGeeks community and help create better learning resources for all.
geeksforgeeks-improvement-icon
Suggest Changes
min 4 words, max Words Limit:1000

Thank You!

Your suggestions are valuable to us.

What kind of Experience do you want to share?

Interview Experiences
Admission Experiences
Career Journeys
Work Experiences
Campus Experiences
Competitive Exam Experiences