How to use the LOOKUP function in Excel



LOOKUP Function Overview The LOOKUP function is similar to other Excel lookup functions such as VLOOKUP and HLOOKUP in that it can be used to return a value from a table of data in Excel.How it differs is that while VLOOKUP only returns data from columns of data and HLOOKUP only from rows, the LOOKUP function can return data from a vector - a single row or column of data - or from an array - a block of data containing multiple rows or columns.To accomplish this, the function has two forms: the Vector Form and the Array Form. Vector and Array FormsThe Vector Form will search a single row or column for a specified value and then return a value from the same position in a second row or column.The Array form looks in the first row or column of a block of data for the specified value, and then returns a value from the same position in the last row or column of the block.This tutorial focuses on the Vector Form of the function.The LOOKUP Function Syntax and Arguments - Vector FormThe syntax for the Vector Form of the LOOKUP function is:= LOOKUP(lookup_value, lookup_vector, [result_vector]) Lookup_value (required) - a value that the function searches for in the first vector. The Lookup_value can be a number, text, a logical value, or a name or cell reference that refers to a value. Lookup_vector (required) - a range containing only one row or column that the function searches to find the Lookup_value. The data can be text, numbers, or logical values. Result_vector (optional) - a range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.Notes:For the LOOKUP function to work correctly, the Lookup_vector must be sorted in ascending order (A to Z or smallest to largest for numbers)If the Result_vector argument is omitted, the function returns the Lookup_value argument if it is present in the Lookup_vectorIf the function cannot find an exact match for the Lookup_value, it chooses the largest value in the Lookup_vector that is less than or equal in value to the Lookup_valueIf the Lookup_value is smaller than all values in the Lookup_vector, the LOOKUP function will return an #N/A errorExample Using the Vector Form of the LOOKUP FunctionAs seen in the image above, this example will use the Vector Form of the LOOKUP function to find the price of a Gear in the inventory list.Entering the Tutorial DataEnter the following data into cells D1 to F5.Cell Data D1 - Part Name E1 - Price D4 - Part D5 - Bearing D6 - Bolt D7 - Cog D8 - Gear D9 - Washer D10 - Widget E4 - Price E5 - $17.34 E6 - $1.54 E7 - $20.21 E8 - $23.56 E9 - $1.43 E10 - $14.76Entering the Function's ArgumentsClick on cell E2 in the worksheet - this is where the results of the function will be displayedClick on the Formulas tab of the ribbon menuChoose Lookup and Reference from the ribbon to open the function drop down listClick on the LOOKUP in the list to bring up the Select arguments dialog box Click on the lookup_value, lookup_vector, result_vector option in the listClick OK to bring up the Function Arguments dialog box In the dialog box, click on the Lookup_value lineClick on cell D2 in the worksheet to enter that cell reference into the dialog box - in this cell we will type the part name that we are searching forClick on the Lookup_vector line in the dialog boxHighlight cells D5 to D10 in the worksheet to enter this range in the dialog box - this range contains the part namesClick on the Result_vector line in the dialog boxHighlight cells E5 to E10 in the worksheet to enter this range in the dialog box - this range contains the prices for the list of partsCompleting the FunctionClick OK to complete the function and close the dialog boxAn #N/A error appears in cell E2 because we have yet to type a part name in cell D2Click on cell D2, type Gear and press the Enter key on the keyboardThe value $20.21 should appear in cell E2 as this is the price of a gear located in second column of the data tableTest the function by typing other part names into cell D2. The price for each part in the list will appear in cell E2 When you click on cell E2 the complete function =LOOKUP (D2, D5:D10, E5:E10) appears in the formula bar above the worksheetHow to use the LOOKUP function in ExcelMORE INFORMATIONThe LOOKUP function returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value, and then returns a value from the same position in a second one-row or one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified value, and then returns a value from the same position in the last row or column of the array. Vector Form of LOOKUPThe vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value, and then returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values that you want to match. Syntax for Vector FormLOOKUP(lookup_value,lookup_vector,result_vector)Lookup_value is a value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.Lookup_vector is a range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values. Important The values in lookup_vector must be placed in ascending order. For example, -2, -1, 0, 1, 2 or A-Z or FALSE, TRUE. If you do not do so, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.NoteIf LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.ExampleAB1FrequencyColor24.14red34.19orange45.17yellow55.77green66.39blueFormulaDescription (Result)=LOOKUP(4.91,A2:A6,B2:B6)Looks up 4.19 in column A, and returns the value from column B that is in the same row (orange).=LOOKUP(5.00,A2:A6,B2:B6)Looks up 5.00 in column A, and returns the value from column B that is in the same row (orange). =LOOKUP(7.66,A2:A6,B2:B6)Looks up 7.66 in column A, matches the next smallest value (6.39), and returns the value from column B that is in the same row (blue). =LOOKUP(0,A2:A6,B2:B6)Looks up 0 in column A, and returns an error because 0 is less than the smallest value in the lookup_vector A2:A7 (#N/A). Array Form of LOOKUPThe array form of LOOKUP looks in the first row or column of an array for the value that you specify, and then returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values you want to match are in the first row or column of the array. Syntax for Array FormLOOKUP(lookup_value,array)Lookup_value is a value that LOOKUP searches for in an array. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. If LOOKUP cannot find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value. Array is a range of cells that contains text, numbers, or logical values that you want to compare with lookup_value. The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array. If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for lookup_value in the first row. If array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column. With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column. Important The values in array must be placed in ascending order. For example, -2, -1, 0, 1, 2 or A-Z or FALSE, TRUE. If you do not do so, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.ExampleAB1a12b23c34d4FormulaDescription (Result)=LOOKUP("c",A1:B4)Looks up "C" in first row of the array and returns the value in the last row that is in the same column (3).=LOOKUP("bump",A1:B4)Looks up "bump" in first row of the array and returns the value in the last column that is in the same row (2).=LOOKUP(E74, Exchange!A:A,(Exchange!B:B))Create the Sample WorksheetThis article uses a sample worksheet to illustrate Excel's built-in functions, for example referencing a name from column A and returning the age of that person from column C. To create this worksheet, enter the following data into a blank Excel worksheet.You will type the value that you want to find into cell E2. You can type the formula in any blank cell in the same worksheet.ABCDE1NameDeptAgeFind Value2Henry50128Mary3Stan201194Mary101225Larry30129Term DefinitionsThis article uses the following terms to describe the Excel built-in functions:TermDefinitionExampleTable_ArrayThe whole lookup table.A2:C5Lookup_ValueThe value to be found in the first column of Table_Array. E2Lookup_Array-or-Lookup_VectorThe range of cells that contains possible lookup values.A2:A5Col_Index_NumThe column number in Table_Array the matching value should be returned for.3 (third column in Table_Array)Result_Array-or-Result_VectorA range that contains only one row or column. It must be the same size as Lookup_Array or Lookup_Vector.C2:C5Range_LookupA logical value (TRUE or FALSE). If TRUE or omitted, an approximate match is returned. If FALSE, it will look for an exact match.FALSETop_CellThis is the reference from which you want to base the offset. Top_Cell must refer to a cell or range of adjacent cells. Otherwise, OFFSET returns the #VALUE! error value.Offset_ColThis is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. For example, "5" as the Offset_Col argument specifies that the upper-left cell in the reference is five columns to the right of reference. Offset_Col can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference). FunctionsLOOKUP()The LOOKUP function finds a value in a single row or column and matches it with a value in the same position in a different row or column.The following is an example of LOOKUP formula syntax:=LOOKUP(Lookup_Value,Lookup_Vector,Result_Vector) The following formula finds Mary's age in the sample worksheet:=LOOKUP(E2,A2:A5,C2:C5)The formula uses the value "Mary" in cell E2 and finds "Mary" in the lookup vector (column A). The formula then matches the value in the same row in the result vector (column C). Because "Mary" is in row 4, LOOKUP returns the value from row 4 in column C (22).Note The LOOKUP function requires that the table be sorted.For more information about the LOOKUP function, click the following article number to view the article in the Microsoft Knowledge Base: 324986 How to use the LOOKUP function in Excel VLOOKUP()The VLOOKUP or Vertical Lookup function is used when data is listed in columns. This function searches for a value in the left-most column and matches it with data in a specified column in the same row. You can use VLOOKUP to find data in a sorted or unsorted table. The following example uses a table with unsorted data.The following is an example of VLOOKUP formula syntax: =VLOOKUP(Lookup_Value,Table_Array,Col_Index_Num,Range_Lookup) The following formula finds Mary's age in the sample worksheet:=VLOOKUP(E2,A2:C5,3,FALSE)The formula uses the value "Mary" in cell E2 and finds "Mary" in the left-most column (column A). The formula then matches the value in the same row in Column_Index. This example uses "3" as the Column_Index (column C). Because "Mary" is in row 4, VLOOKUP returns the value from row 4 in column C (22).For more information about the VLOOKUP function, click the following article number to view the article in the Microsoft Knowledge Base: 181213 How to Use VLOOKUP or HLOOKUP to find an exact match INDEX() and MATCH()You can use the INDEX and MATCH functions together to get the same results as using LOOKUP or VLOOKUP.The following is an example of the syntax that combines INDEX and MATCH to produce the same results as LOOKUP and VLOOKUP in the previous examples:=INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num) The following formula finds Mary's age in the sample worksheet: =INDEX(A2:C5,MATCH(E2,A2:A5,0),3)?The formula uses the value "Mary" in cell E2 and finds "Mary" in column A. It then matches the value in the same row in column C. Because "Mary" is in row 4, the formula returns the value from row 4 in column C (22).Note If none of the cells in Lookup_Array match Lookup_Value ("Mary"), this formula will return #N/A. For more information about the INDEX function, click the following article number to view the article in the Microsoft Knowledge Base: 324988 How to use the INDEX function to find data in a table OFFSET() and MATCH()You can use the OFFSET and MATCH functions together to produce the same results as the functions in the previous example.The following is an example of syntax that combines OFFSET and MATCH to produce the same results as LOOKUP and VLOOKUP:=OFFSET(top_cell,MATCH(Lookup_Value,Lookup_Array,0),Offset_Col)This formula finds Mary's age in the sample worksheet: =OFFSET(A1,MATCH(E2,A2:A5,0),2)The formula uses the value "Mary" in cell E2 and finds "Mary" in column A. The formula then matches the value in the same row but two columns to the right (column C). Because "Mary" is in column A, the formula returns the value in row 4 in column C (22).For more information about the OFFSET function, click the following article number to view the article in the Microsoft Knowledge Base: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download