The XLOOKUP function is easier to use and has some additional advantages.Enter the fields into the formula builder on the right.Ĩ. If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful formulas Excel has to offer. To perform advanced lookups, you'll need INDEX and MATCH. Instead of using VLOOKUP, use INDEX and MATCH. Because we're using named ranges, we can easily copy this VLOOKUP function to the other cells without worrying about cell references. Note: for example, Walker receives a bonus of $1,500. Select cell E4, click on the lower right corner of cell E4 and drag it down to cell E10. Set the fourth argument of the VLOOKUP function to TRUE to return an approximate match.Ĥ. If UK, the VLOOKUP function uses Table1, if USA, the VLOOKUP function uses Table2. The second argument of the VLOOKUP function does the trick. Select cell E4 and enter the VLOOKUP function shown below.Įxplanation: the bonus depends on the market (UK or USA) and the sales amount. Create two named ranges: Table1 and Table2.Ģ. You can use the IF function to check whether a condition is met, and return one lookup table if TRUE and another lookup table if FALSE.ġ.
When using the VLOOKUP function in Excel, you can have multiple lookup tables. For example, the #NAME? error if you accidentally misspell the word VLOOKUP. Remember, the IFERROR function catches other errors as well. If you're using Excel 2010 or Excel 2007, simply replace IFNA with IFERROR. Note: the IFNA function was introduced in Excel 2013. If you like, you can use the IFNA function to replace the #N/A error with a friendly message. For example, the VLOOKUP function below cannot find the value 28 in the leftmost column.Ģ. If the VLOOKUP function cannot find a match, it returns a #N/A error.ġ. Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson. Use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup.ĭo you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column lookup. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance). For example, the VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.Įxplanation: the VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or miA, etc. The VLOOKUP function in Excel performs a case-insensitive lookup. For example, take a look at the VLOOKUP function below.Įxplanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed. If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. No worries, you can use INDEX and MATCH in Excel to perform a left lookup. The VLOOKUP function only looks to the right. Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID.
If you change the column index number (third argument) to 3, the VLOOKUP function looks up the first name and returns the salary.
For example, the VLOOKUP function below looks up the first name and returns the last name.Ģ. The VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right.ġ. Note: always sort the leftmost column of the red table in ascending order if you use the VLOOKUP function in approximate match mode (fourth argument set to TRUE). The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column of the red table. In this example, this will be the value 80.ģ. If the VLOOKUP function cannot find the value 85 in the first column, it will return the largest value smaller than 85. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an approximate match. There's no value 85 in the first column.Ģ. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost column of the red table (second argument). Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth argument set to TRUE).ġ. Instead of returning the salary, the VLOOKUP function below returns the last name (third argument is set to 3) of ID 79. If the VLOOKUP function cannot find the value 53 in the first column, it will return a #N/A error.ģ. Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact match. The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.
0 kommentar(er)
