We even have guides on how to use conditional formatting in Excel to color-code specific cells and how to add comments to your formulas in Microsoft Excel. For example, when you protect a sheet or workbook, all of the cells will be locked, but you can also lock cells individually by right-clicking and selecting "Format Cells." And if you need to, you can also freeze rows and columns by selecting "Freeze Panes" in the View tab.īut not everyone is a fan of Excel, so if you need to convert Excel spreadsheets to Google Sheets, we have a guide for that, as well as a guide on how to open Google Sheets in Excel.įor business users, we also have 10 Excel business tips that can help you keep your job, including guides on how to remove duplicate data, recover lost Excel files, use pivot tables to summarize data, and more. There are a number of neat tips that'll help you out when you're managing your Excel spreadsheets. For example, if you have one worksheet with names and phone numbers and another sheet with names and email addresses, you can put the email addresses next to the names and phone numbers by using VLOOKUP.
Using VLOOKUP, you can not only search for individual values, but also combine two worksheets into one. Finally, the search_mode argument is set to 1, which means the function will search from the first item to the last.Enter the value whose data you're searching for. The match_mode argument is set to 1, which means the function will look for an exact match, and if it can't find one, it returns the next larger item. It sets the if_not_found argument to return 0 (zero) if nothing is found. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so a single formula can return both employee name and department from cells C5:D14.Įxample 3 adds an if_not_found argument to the preceding example.Įxample 4 looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)Įxample 2 looks up employee information based on an employee ID number. Note: XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number. It doesn't include the match_mode argument, as XLOOKUP produces an exact match by default. It includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. If not sorted, invalid results will be returned.Įxample 1 uses XLOOKUP to look up a country name in a range, and then return its telephone country code. Excel adds the range you select as you highlight a table or area of your worksheet. Also, Excel provides information below the text boxes to help you understand each argument. Here is a manually entered employee name so that you can see what Excel sees. 2 - Perform a binary search that relies on lookup_array being sorted in descending order. Excel displays the cell’s value to the right of the text box, helping you check your entry. If not sorted, invalid results will be returned. 1 - Perform a reverse search starting at the last item.Ģ - Perform a binary search that relies on lookup_array being sorted in ascending order. If you have more than 1,000 unique items in the list, only the first. If none found, return the next larger item.Ģ - A wildcard match where *, ?, and ~ have special meaning.ġ - Perform a search starting at the first item. In Excel 2003, the AutoFilter DropDown list will show the first 1,000 unique items. If none found, return the next smaller item.ġ - Exact match. If a valid match is not found, and is missing, #N/A is returned.Ġ - Exact match. Where a valid match is not found, return the text you supply. If one wants to look up a value in one column. You supply the column number as the colindexnum argument, which tells VLOOKUP which column contains the data you seek. How this works: You provide a name or lookupvalue that tells VLOOKUP which row of the data table to look for the desired data. *If omitted, XLOOKUP returns blank cells it finds in lookup_array. VLOOKUP looks for a value in the leftmost column of a table, and then returns a value to the right of that column. VLOOKUP typically returns a single field of data as its output.
=XLOOKUP(lookup_value, lookup_array, return_array, , , ) If no match exists, then XLOOKUP can return the closest (approximate) match. The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds.