Excel Functions Tutorials
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- HLOOKUP Formula in Excel
- HLOOKUP Examples
- Hyperlink Excel Function
- Hyperlink Formula in Excel
- INDIRECT Function in Excel
- LOOKUP Excel Function
- LOOKUP Formula in Excel
- Match Excel Function
- MATCH Formula in Excel
- How to Match Data in Excel?
- VLOOKUP Excel Function
- VLOOKUP Formula
- VLOOKUP Tutorial in Excel
- VLookup in VBA Excel
- VLOOKUP in Pivot Table
- VLOOKUP with SUM
- VLOOKUP with Match
- SUMIF With VLOOKUP
- VLookup with IF Statement
- Vlookup to the Left
- VLOOKUP from Another Sheet / Workbook
- VLOOKUP Examples in Excel
- VLOOKUP Table Array
- VLOOKUP vs HLOOKUP
- INDEX Excel Function
- INDEX Formula in Excel
- Indirect Formula in Excel
- TRANSPOSE Excel Function
- Row Function in Excel
- Rows Function in Excel
- OFFSET Excel Function
- OFFSET Formula in Excel
- VLOOKUP with Multiple Criteria
- IFERROR with VLOOKUP in Excel
- Fixing VLOOKUP Errors
- Alternatives to Vlookup
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
Table of Contents
As you know 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. 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 lookup function with some examples.
Lookup Function with Excel Alternative to Vlookup – Example #1
Let’s consider a data of Name country and age and apply the lookup function.
Select the name entered in F4 cell and then apply the below lookup formula =LOOKUP(F4,A3:A19,C3:C19).
To get the age column
Right-to-Left Functionality Using Lookup – Example #2
The biggest benefits of lookup function are that it can operate from right to left. You will see the processing of lookup function in the below example.
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.
And get the output of the corresponding name
#2 – INDEX / MATCH Function as Vlookup Alternatives
Example #3 – Using Index Match
Let’s consider below data and find out the age by using the Name from the table by using the normal index match and vlookup formula.
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.
Example #4 – Right-to-Left Functionality Using Index Match
Let’s consider below data and find out the Name by using the Age from the table by using the normal index match and vlookup formula.
As you can easily see the that by using 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.
Things to Remember about the Alternatives to Vlookup in Excel
- Using the index match is far better than using simple vlookup function.
- Index match can lookup right to left.
- Insert and deletion column safely.
- No limit for 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
This has been 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 –