Which function is best used to lookup and retrieve data from a specific row in a table?
|
Looks through a sorted row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column. The If In the When using the ExamplesThis examples shows the price of part number 126.
This examples shows when the search_key is not found (student ID 765333), a non-exact match may be returned.
See Also
Was this helpful? How can we improve it? Learning Outcomes
Lookup functions in Excel are used for looking through a single column or row to find a particular value from the same place in a second column or row. This often takes place when there are multiple worksheets within a workbook or a large amount of data in a worksheet. Lookup functions are very helpful with large sets of data to find specific information across many rows or columns. VLOOKUP (Vertical Lookup)Looks for a value in the leftmost column of a table and returns a value in the same row from the vertical column you define. The lookup value must always be in the leftmost column of the table. The Excel formula is written =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) This table includes the sales totals for salespeople. In this case you want to find out what the sales were for certain salespeople. This is a small example but imagine a long list of names and numbers to search through. Using the VLOOKUP function quickly brings you the results you want. Be sure to use F4 to create the absolute references and keep the table array the same for each lookup. Also make sure and make the col_index_num use the second column in the table (column 2 in the table) with a return for the range_lookup as FALSE. By using FALSE in all cases, there is no need to sort your tables into ascending order. Here is what those results look like.  For another VLOOKUP example, watch this video of how to use this function to find parts that are in stock and the supplier for particular parts. HLOOKUP (Horizontal Lookup)Looks for a value in the topmost row of a table and returns a value in the same row from the horizontal column you define. The lookup value must always be in the leftmost column of the table. The Excel formula is written =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) MATCH and INDEX FunctionsNow we’ll learn two additional lookup functions, Match and Index. Watch this video to learn how to use Match and Index lookup functions. Here are the descriptions and formulas for creating them in Excel.
As you become more familiar with lookup functions, try out a few more by selecting the Formulas tab, Lookup & Reference button and see what others are available for you to try. PRactice QuestionsContribute!Did you have an idea for improving this content? We’d love your input. Improve this pageLearn More Which function is best used to LOOKUP and retrieve data from a specific row in a table match address VLOOKUP Hlookup?HLOOKUP performs the same function as VLOOKUP, but looks up data that has been formatted by rows. HLOOKUP searches for a value in the top row of a table (or an array of values), and then returns a value in the same column from a row you specify in the table or array.
Which function is used to retrieve data from a specific row in a table?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.
Which function is used to LOOKUP and retrieve data?Well, a lookup function will help you find values in a table of data and insert them in another worksheet location. The syntax for a lookup function-- this is the HLOOKUP, and it's very similar to the VLOOKUP. The only change is our third argument is row index number instead of column index number.
What is the best LOOKUP function in Excel?Excel's Best Lookup Method: INDEX-MATCH
lookup_value—The value to match in lookup_array. lookup_array—A range of cells with data. match_type—Specifies how Excel matches the lookup_value with values in the lookup_array. For exact matches, always use 0 for this argument.
|
