WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Alternatives to Vlookup

Alternatives to Vlookup

Vlookup is a very useful function to reference in columns but there are alternatives to vlookup too because Vlookup has its limitations as it can reference from left to right, to reference from right to left we use a combination of Index and Match function combined which is one of the best alternatives to Vlookup in excel.

Vlookup Alternatives

As you know, the Vlookup function is used to lookup a value in a list and return any column from the lookup column. But if your lookup column is not the first one, then Vlookup will not work here. You need to copy the lookup column to a newly inserted first column, or you can apply the Lookup and index match combination to lookup a value across a row and return a value from a column.

#1 – LOOKUP Function as Vlookup Alternatives

Here we use lookup and index match as alternatives to vlookup in excel. The lookup function is better than Vlookup as it is less restrictive in use. It was first introduced in MS 2016.

You can search data both vertically and horizontally. It also allows left to right and right to left lookup; however, excel alternatives to vlookup only allow left to right. Here understand the working of the lookup function with some examples.

Lookup Function with Excel Alternative to Vlookup – Example #1

Let’s consider the data of Name, country, and age and apply the lookup function.

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

lookup function example 1

Select the Name entered in F4 cell and then apply the below lookup formula =LOOKUP(F4,A3:A19,C3:C19).

lookup function example 1-1

To get the age column

lookup function example 1-2

Right-to-Left Functionality Using Lookup – Example #2

The most significant benefits of the lookup function are that it can operate from right to left. You will see the processing of the lookup function in excel in the below example.

lookup function example 2

If you want to switch the procedure to search for age and output the corresponding name, it works for LOOKUP but produces an error for alternatives to VLOOKUP.

=LOOKUP(F3,C2:C18,A2:A18)

lookup function example 2-1

And get the output of the corresponding Name

lookup function example 2-2

#2 – INDEX / MATCH Function as Vlookup Alternatives

Example #3 – Using Index Match

Let’s consider the below data and find out the age by using the Name from the table by using the standard index match and vlookup formula.

Index match as Vlookup alternatives example 1

=INDEX($I$2:$K$19,MATCH(M6,$I$2:$I$19,0),3)

Index match as Vlookup alternatives example 1-1

=VLOOKUP(M6,I2:K19,3,0)

Index match as Vlookup alternatives example 1-2

Here you will get the desired same output from both the formulas as both the formula can able to search the age from the data.

Index match as Vlookup alternatives example 1-3

Example #4 – Right-to-Left Functionality Using Index Match

Let’s consider the below data and find out the Name using the age from the table by using the standard index match and vlookup formula.

Using Index Match Example 1

=INDEX(R24:T41,MATCH(M27,$K$24:$K$40,0),1)

Using Index Match Example 1-1

=VLOOKUP(M27,I23:K40,3,0)

Using Index Match Example 1-2

As you can easily see the that by using an index match, you will get the Name from the table, but #N/A error from the vlookup formulas as vlookup is not able to lookup value from left-right.

Using Index Match Example 1-3

Things to Remember about the Alternatives to Vlookup in Excel

  • Using the index match is far better than using a simple vlookup function.
  • The index match can lookup right to left.
  • Insert and deletion column safely.
  • No limit to a lookup value’s size while using the VLOOKUP function. The length of your lookup criteria should not exceed 255 characters; otherwise, it will through #Value error in excel.
  • Higher processing speed than normal Vlookup.

You can download these Alternatives to Vlookup in Excel template here – Alternatives to Vlookup Excel Template.

Recommended Articles

This has been a guide to Alternatives to Vlookup in Excel. Here we discuss examples of better alternatives to Vlookup in Excel Using 1) Lookup Function and 2) Index Match combination along with downloadable excel templates. You may also look at these useful functions in excel –

  • Vlookup to the Left
  • Statement of VLookup with IF
  • VLookup in Excel VBA
  • Multiple Criteria of VLOOKUP
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

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?

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More