Excel Archives | Page 2 of 3

Excel can select a specific value from a long list of values. These are called lookup functions. The most important lookup functions are VLOOKUP and XLOOKUP. Retrieving Data with Lookup Functions Data is often stored in a list on a worksheet. A list that categorizes values is called a lookup table.The lookup and reference functions allow you to find a value based on the lookup table criteria. These functions allow the use of different input values based on criteria to produce an output. Retrieving from Columns and Rows VLOOKUP and HLOOKUP are common lookup functions. VLOOKUP is a vertical lookup which works when the lookup table is organized in columns. HLOOKUP is a horizontal lookup, so the lookup table needs to be organized in rows. VLOOKUP VLOOKUP retrieves data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The “V” stands for “vertical”. Lookup values must appear in the first column of the table, with lookup columns to the right. =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) lookup_value - The value to look for in the first column of a table.table_array - The table from which to retrieve a value.col_index_num - The column in the table from which to retrieve a value.range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match ArgumentDescriptionlookup_valueData to look up; can be number, text, logical value, name, or cell referencetable_arrayThe range containing the data you want to search; the lookup_value must be in the first columncol_index_numThe number of the column containing the data you want to retrieve; 1, 2, 3, etc.range_lookupTRUE: greatest value not exceeding the lookup_value; FALSE: exact matchVLOOKUP HLOOKUP HLOOKUP retrieves data from a specific row in table. The “H” in HLOOKUP stands for “horizontal”, where lookup values appear in the first row of the table, with the data below the first row. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches. =HLOOKUP (value, table, row_index, [range_lookup]) value - The value to look up.table - The table from which to retrieve data.row_index - The row number from which to retrieve data.range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match ArgumentDescriptionlookup_valueData to look up; can be number, text, logical value, name, or cell referencetable_arrayThe range containing the data you want to search; the lookup_value must be in the first rowrow_indexThe number of the row containing the data you want to retrieve; 1, 2, 3, etc.range_lookupTRUE: greatest value not exceeding the lookup_value; FALSE: exact matchHLOOKUP New function XLOOKUP XLOOKUP is a new function that was released in early 2020. It is a new function available for Office 365 subscribers. XLOOKUP is intended to replace both VLOOKUP and HLOOKUP. If you need your spreadsheets to be compatible with others with older versions of Excel, you still need to use VLOOKUP or HLOOKUP. For new spreadsheets for your personal use, you should start using XLOOKUP. For more on this new function, see XLOOKUP Tutorial for Beginners XLOOKUP Searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used. =XLOOKUP(lookup_value,lookup_array,return_array,match_mode,search_mode) Lookup_value - is the value to search for.Lookup_array - is the array or range to search.Return_array - is the array or range to return.Match_mode - specify how to match lookup_value against the values in lookup_array.Search_mode - specify the search mode to use. By default, a first to last search will be used. ArgumentDescriptionlookup_valuevalue to search for; can be number, text, logical value, name, or cell referencelookup_arrayThe range containing the data you want to searchreturn_arrayThe range containing the data to retrievematch_mode0 exact match, -1 exact match or next smaller item, 1 exact match or next larger item, 2 wildcard character matchsearch_mode1 search first to last, -1 search last to firstXLOOKUP LOOKUP 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. =LOOKUP (lookup_value, lookup_vector, [result_vector]) lookup_value - The value to search for.lookup_vector - The one-row, or one-column range to search.result_vector - [optional] The one-row, or one-column range of results. ArgumentDescriptionlookup_valuevalue that LOOKUP searches for in Lookup_vector and can be a number, text, a logical value, or a name or reference to a value.lookup_vectorrange that contains only one row or one column of text, numbers, or logical values, placed in ascending order.result_vectorrange that contains only one row or column, the same size as Lookup_vector.LOOKUP Retrieving from Multidimensional Tables VLOOKUP can retrieve data from columns and HLOOKUP retrieves from rows. The INDEX function can retrieve data from multidimensional tables (both columns and rows). The INDEX function can retrieve a value when given a row and column. The INDEX function is often used with the MATCH function that can feed both the column and row to the INDEX function. ArgumentDescriptionINDEXReturns a value or reference of the cell at the intersection of a particular row and column, in a given range.MATCHReturns the relative position of an item in an array that matches a specified value in a specified order.CHOOSEChooses a value or action to perform from a list of values, based on an index number.INDIRECTReturns the reference specified by a text stringOFFSETReturns a reference to a range that is a given number of rows and columns from a given reference. INDEX/MATCH INDEX is often used with the MATCH function(s) to achieve a similar result to using the VLOOKUP. The formula would be INDEX(array,MATCH,MATCH). INDEX Returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX. =INDEX (array, row_num, [col_num], [area_num]) Array Form=INDEX (array, row_num, [col_num]) Reference Form=INDEX (reference, row_num, [col_num], [area_num]) array - a range of cells, or an array constantrow_num - The row position in the reference or array.col_num - [optional] The column position in the reference or array.area_num - [optional] The range in reference that should be used. MATCH Locates the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH. =MATCH (lookup_value, lookup_array, [match_type]) lookup_value - The value to match in lookup_array.lookup_array - A range of cells or an array reference.match_type - [optional] How to match, specified as –1, 0, or 1. Default is 1. CHOOSE CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(2,“red”,“blue”,“green”) returns “blue”, since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references. =CHOOSE (index_num, value1, [value2], …) index_num - The value to choose. A number between 1 and 254.value1 - The first value from which to choose.value2 - [optional] The second value from which to choose. IS Functions ArgumentDescriptionISBLANKvalue is an empty cellISERRany error value except #N/AISERRORany error valueISLOGICALlogical valueISNA#N/A error valueISNONTEXTany item that is not textISNUMBERvalue is a numberISREFvalue is a referenceISTEXTvalue is a text ISBLANK ISBLANK function returns TRUE when a cell contains is empty, and FALSE when a cell is not empty. For example, if A1 contains “apple”, ISBLANK(A1) returns FALSE. =ISBLANK (value) value - The value to check. IFERROR IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements. Find and handle errors: errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. =IFERROR (value, value_if_error) value - The value, reference, or formula to check for an error.value_if_error - The value to return if an error is found. Dynamic Arrays There are Excel formulas that can return arrays of variable sizes. These new functions are called dynamic arrays. The dynamic array functions can spill into additional cells. FunctionDescriptionFILTERfilter a range or arrayRANDARRAYReturns an array of random numbersSEQUENCEReturns a sequence of numbersSORTSorts a range or arraySORTBYSorts a range or array based on the values in a corresponding range or arrayUNIQUEReturns the unique values from a range or array@Prevent from spilling Video: Excel VLOOKUP Function Examples https://youtu.be/VVD1GXPMwIg Video: Excel XLOOKUP Function Examples https://youtu.be/4NyXbNVQ7s8