It should be blank, so your VLOOKUP result doesnt overwrite anything.

In this example, well look for pop-singer Lizzos Instagram followers so we would enter Lizzo into the text box.

The function is case sensitive, so enter the data carefully.

Remember: VLOOKUP searches for data vertically, so be sure to enter the column letter followed by the row number.

For this example, we want to search through all the data, encompassing all fields on the worksheet, so well enter A2:B16.

In our example, Instagram followers are represented in column B, so we would enter the numerical value of B, which is two (its the second column).

From our example, we now know Lizzo has 6.4 million Instagram followers without having to manually search the list for it.

Since we launched in 2006, our articles have been read more than 1 billion times.

This new function solves some of VLOOKUPs limitations and has extra functionality.

For example, XLOOKUP can look to its left, defaults to an exact match, and allows you to specify a range of cells instead of a column number.

Anyone can join the Insiders program to access the newest Excel features as soon as they become available.

Microsoft will soon begin to roll it out to all Office 365 users.

We want to return the department from column F for each ID in column A.

The XLOOKUP function requires just three pieces of information.

This is no longer an issue because XLOOKUP enables you to select the range to return from (column F in this example).

If that happened in your spreadsheet, the return range would adjust automatically.

This reduces the need to answer that fifth argument and ensures fewer mistakes by users new to the formula.

With XLOOKUP, the order of the table columns does not matter.

Lets see it in action with the previous example, but with a mistyped ID.

We want to return the discount dependent upon the amount spent.

We need to know where the values in column B fall within the ranges in column E.

There is also an option to use wildcard characters (2) such as the or the.

This should have returned a 0 discount because spending 64 does not reach the criteria for any discount.

It is not a problem with having the 0 at the bottom of the range.

It looks along row 4 to find the name, and returns the value from row 5: XLOOKUP(A2,B4:E4,B5:E5).

This enables us to switch the lookup to start at the bottom and look up a list to find the last occurrence of a value instead.

It is optional, and we wanted the default of an exact match.

One of which is that XLOOKUP can be used across sheets, workbooks and also with tables.

The examples were kept simple in the article to help our understanding.

On most days, he can be found teaching Excel in a classroom or seminar.

Alan gets a buzz from helping people improve their productivity and working lives with Excel.

How To Do A Vlookup In Excel For Mac