With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on. The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. LOOKUP's default behavior makes it useful for solving certain problems in Excel. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the LOOKUP function can be entered as part of a formula in a cell of a worksheet. There are 2 different syntaxes for the LOOKUP function. The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain. If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.
Vlookup is the most widely used function in Excel. As we all know Vlookup searches for the given value in a table and brings the value of that column or the column which is in the right side. That means by default Vlookup can look right side in the table. However with the combination of other functions we can make Vlookup to look from right to left. In this article let us understand how to use Vlookup to the left side search.
How to use Vlookup to the left search
There are three methods to do this. Two methods are with vlookup formula and one more is combination of INDEX and MATCH which is alternate to VLOOKUP FUNCTION.
- Vlookup with IF array formula
- Vlookup with CHOOSE array formula
- INDEX MATCH to lookup left
1. Vlookup with IF array formula
To lookup left, we can use IF function while defining table range in vlookup formula. Detail about how to enter if formula is explained below.
2. Vlookup with CHOOSE array formula
Similar to above formula, instead of IF function, we can use CHOOSE function while defining the table range in Vlookup. CHOOSE with Vlookup can do much more than this, i will explain that in our next post. The details about how to use this formula is explained below.
3. INDEX MATCH to lookup left
The above two formulas will make search Vlookup to the left, but when the data in the table is large, these above formulas will take lot of time and slow down your Excel. To overcome this issue, alternate formula is combination of INDEX and MATCH to perform same task easily.
INDEX function return the value of an element in a table based on row number and column number. MATCH function searches for the given value in a range and return the position for matching value.
Excel Vba Dlookup
The combination of both these functions will be an alternative for Vlookup function. INDEX MATCH is a powerful formula which will do everything which Vlookup dose and much more.
These are the three methods to vlookup to the left in excel. You can download the file with formula in below link.