Create an Excel Link Lookup Formula with VLOOKUP

Contents
  1. Combine VLOOKUP and CHOOSE in Excel to create a lookup formula on the left
  2. Textbook. Using the VLOOKUP and CHOOSE functions in a link lookup formula
  3. Enter training data
  4. Open the VLOOKUP dialog
  5. Entering arguments in the VLOOKUP dialog
  6. Search value
  7. Opening the SELECT function
  8. Enter functions
  9. Column number
  10. Search scope
  11. Return data with link lookup formula
  12. Create a two-column array of tables
  13. Rearrange columns with the SELECT function

Combine VLOOKUP and CHOOSE in Excel to create a lookup formula on the left

The Excel VLOOKUP function is used to find and return information from a data table based on the lookup value you select.

Normally VLOOKUP requires that the lookup value be in the leftmost column of the data table, and the function returns another data field that is in the same row to the right of that value.

However, by combining VLOOKUP with the CHOOSE function, you can look up a formula for links that:

  • Allows you to search for a value from any column in the data table

  • Returns the information in a column to the left of the search value

These instructions apply to versions of Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Textbook. Using the VLOOKUP and CHOOSE functions in a link lookup formula

Use the formula to create the search formula on the left in the sample image:

=VLOOKUP($D$2, SELECT({1,2}, $F:$F, $D:$D), 2, FALSE)

In this example, the formula finds parts supplied by the various companies listed in column 3 of the datasheet.

The purpose of the CHOOSE function in the formula is to make VLOOKUP think that column 3 is column 1. As a result, the company name can be used as a lookup value to find the name of the part provided by each company.

Enter training data

  1. Enter title The supplier in a cell D1

  2. Enter title Detail in a cell E1

  3. Enter the data table in the image above into the cells D4 to F9

  4. strings 2 and 3 left blank to match the search criteria and the left search formula created during this tutorial.

Open the VLOOKUP dialog

Although a formula can be entered directly into cell F1 on a worksheet, many people struggle with formula syntax.

In this case, it is safer to use the VLOOKUP dialog box. Almost all Excel functions have a dialog box where you can enter each of the function’s arguments on a separate line.

  1. Click on a cell E2 worksheet. E2 is where the results of the left search formula are displayed.

  2. Click on the tab formulas on the tape.

  3. click on the link Search and help on the ribbon to open the features drop-down list.

  4. Click VLOOKUP in the list to open the function’s dialog box.

Entering arguments in the VLOOKUP dialog

Function arguments are the values ​​used by the function to calculate the result.

In the function’s dialog box, the name of each argument is on a separate line, followed by a field for entering a value.

Enter the following values ​​for each of the VLOOKUP arguments in the appropriate line of the dialog box, as shown in the attached image.

Search value

The lookup value is a field of information that is used to search a series of tables. VLOOKUP returns another data field from the same row as the lookup value.

This example uses a cell reference for the location where the company name is entered in the table. This has the advantage that it is easy to change the company name without changing the formula.

  1. Click on a line lookup value in the dialog box.

  2. Click on a cell D2 to add a link to that cell in a row lookup value

  3. Press key F4 on the keyboard to make the cell reference absolute − $D$2.

Absolute cell references are used for lookup values ​​and table arguments to avoid errors when the lookup formula is copied to other cells on the worksheet.

Opening the SELECT function

The table array argument is a block of contiguous data from which specific information is extracted.

Normally VLOOKUP only looks to the right of the lookup value argument to find data in the table array. To make it look left, you have to trick VLOOKUP by rearranging the columns in the table array using the CHOOSE function.

In this formula, the CHOOSE function does two things:

  1. It creates an array of tables that is only two columns wide (columns D and F).

  2. It rearranges the columns in the table matrix from right to left, so column F comes first and column D comes second.

Enter functions

When entering functions manually, each of the function arguments must be separated by a comma.

  1. In the VLOOKUP function window, click on the line table_array

  2. Enter the following function: CHOOSE SELECT({1,2}, $F:$F, $D:$D)

Column number

Usually, the column ordinal indicates which column of the table array contains the data you are aiming for. However, in this formula, it refers to the order of the columns set by the CHOOSE function.

The CHOOSE function creates an array of tables two columns wide, followed first by column F and then by column D. Since the information you are looking for, the member name, is in column D, the value of the column index argument must be 2 .

  1. Click on a line Col_index_num in the dialog box.

  2. Enter 2 in this line.

Search scope

The Range_lookup argument for VLOOKUP is a boolean value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or approximate match to the lookup value.

  • If TRUE or this argument is omitted, VLOOKUP returns an exact match to Lookup_value, or if no exact match is found, VLOOKUP returns the next largest value. To do this with the formula, the data in the first column of the Table_array must be sorted in ascending order.

  • If FALSE, VLOOKUP only uses the exact match of Lookup_value. If there are two or more values ​​in the first column of Table_array that match the lookup value, the first value found is used. If no exact match is found, an #N/A error is returned.

Since we are looking for a specific part name in this tutorial, Range_lookup is set to: not true so that only exact matches are returned by the formula.

  1. Click on a line range_lookup in the dialog box.

  2. Enter a word not true on this line to indicate that we want VLOOKUP to return an exact match for the data we are looking for.

  3. Click Okay to enter the search formula on the left and close the dialog box.

  4. Since we haven’t entered a company name in cell D2, the #N/A error appears in cell E2.

Return data with link lookup formula

To find out which companies supply which parts, enter the name of the company in the cell D2 and press the key ENTER on keyboard.

The part name is displayed in cell E2.

  1. Click on a cell D2 on the worksheet.

  2. Enter Gadget Plus in a cell D2 and press the key ENTER on keyboard.

  3. The text “Gadgets” – the component provided by Gadgets Plus – should appear in cell E2.

Then test your search formula by entering other company names in cell D2, and the corresponding part name should appear in cell E2.

If an error message appears in cell E2, such as #N/A, check for spelling errors in cell D2.

Create a two-column array of tables

The syntax for the CHOOSE function is:

= CHOOSE(index_value, value1, value2, … value254)

The CHOOSE function typically returns one value from a list of values ​​(Value1 to Value254) based on the index number entered.

If it is ordinal 1, the function returns Value1 from the list; if the index is 2, the function returns Value2 from the list, and so on.

When multiple sequence numbers are entered, the function returns multiple values ​​in random order. CHOOSING to return multiple values ​​is done by creating an array.

To enter an array, enclose the entered numbers in curly brackets or parentheses. Two digits are entered for the index number: {1,2}

Note that CHOOSE is not limited to creating a two-column table. By including an optional number in the array, such as {1,2,3}, and an optional range in the value argument, CHOOSE creates a table with three columns.

Additional columns allow you to return different information using the left lookup formula by changing the index number argument of the VLOOKUP column to the number of the column that contains the information you want.

Rearrange columns with the SELECT function

In the CHOOSE function used in this formula:

SELECT({1,2}, $F:$F, $D:$D)

the range for column F is listed before column D.

Since the CHOOSE function sets an array of VLOOKUP tables (the data source for this function), the column order switch in the CHOOSE function is passed to VLOOKUP.

As for VLOOKUP, the table array is only two columns wide, column F on the left and column D on the right. Since column F contains the name of the company we want to search for, and column D contains the names of the parts, VLOOKUP can perform its normal search functions when looking for data to the left of the search value.

As a result, VLOOKUP can use the company name to find the part they supply.

Leave a Reply

Your email address will not be published.