1. How To Do Vlookup In Excel With Two Spreadsheets

1. How To Do Vlookup In Excel With Two Spreadsheets

by

1. How To Do Vlookup In Excel With Two Spreadsheets

VLOOKUP is a robust method in Excel that lets you lookup knowledge from a desk primarily based on a particular worth. On this article, we’ll discover tips on how to use VLOOKUP in Excel with two spreadsheets, enabling you to seamlessly retrieve knowledge from a number of sources.

To start, you’ll need to create two separate spreadsheets. Within the first spreadsheet (let’s name it “Knowledge”), enter the info that you simply need to lookup. This knowledge must be organized in a tabular format, with columns representing completely different classes and rows representing particular person information. Within the second spreadsheet (let’s name it “Lookup”), enter the values that you simply need to use for the lookup. These values will be something, resembling buyer names, product codes, or bill numbers.

After you have created your two spreadsheets, you should use the VLOOKUP perform to retrieve knowledge from the “Knowledge” spreadsheet primarily based on the values within the “Lookup” spreadsheet. The syntax of the VLOOKUP perform is as follows: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

Deciding on the Lookup Worth

The lookup worth is the worth in your major spreadsheet that you simply need to use to seek for matching values in your secondary spreadsheet. This worth will be any cell reference or vary of cells, and it have to be of the identical knowledge kind because the column you are looking in your secondary spreadsheet.

**For instance:** For those who’re utilizing VLOOKUP to search out the worth of a product primarily based on its product code, your lookup worth could be the product code cell in your major spreadsheet.

Listed here are some necessary issues when deciding on your lookup worth:

Concerns
1. **Consistency:** The lookup worth have to be constant throughout each spreadsheets. If the lookup worth isn’t constant, VLOOKUP won’t be able to search out the matching values.

2. **Uniqueness:** If potential, the lookup worth must be distinctive. It will be certain that VLOOKUP returns the right matching worth. If the lookup worth isn’t distinctive, VLOOKUP could return a number of matching values, which will be complicated.

3. **Knowledge Sort:** The lookup worth have to be of the identical knowledge kind because the column you are looking in your secondary spreadsheet. If the info sorts don’t match, VLOOKUP won’t be able to search out the matching values.

Getting into the VLOOKUP Method

To enter the VLOOKUP method, comply with these steps:

1. Choose the cell the place you need the consequence to seem.
2. Sort the next method: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
3. Change lookup_value with the worth you need to lookup.
4. Change table_array with the vary of cells containing the info you need to search.
5. Change col_index_num with the column quantity containing the worth you need to return.

6. Optionally available: Change [range_lookup] with FALSE for a precise match or TRUE for an approximate match. If omitted, TRUE is assumed.

Instance

Suppose you could have a desk of worker knowledge within the vary A1:D5:

Emp ID Identify Wage Division
1001 John Smith $50,000 Gross sales
1002 Jane Doe $45,000 Advertising and marketing
1003 Michael Brown $60,000 Engineering
1004 Sarah Jones $35,000 Buyer Service
1005 David Wilson $40,000 Help

To seek out John Smith’s wage, you’d enter the next method in an empty cell:

=VLOOKUP("John Smith", A1:D5, 3, FALSE)

This method would return the worth “$50,000”, which is John Smith’s wage.

Utilizing CHOOSE for Conditional VLOOKUP

The CHOOSE perform can be utilized to carry out a conditional VLOOKUP by specifying a variety of lookup values and a corresponding vary of outcomes. The syntax of the CHOOSE perform is as follows:

“`
=CHOOSE(index_num, value1, value2, …, valueN)
“`

the place:

  • `index_num` is the index of the worth to be returned.
  • `value1`, `value2`, …, `valueN` are the values to be returned, equivalent to the index numbers 1, 2, …, N.

To make use of CHOOSE for conditional VLOOKUP, comply with these steps:

1. Create a desk of lookup values and corresponding outcomes.

2. In a separate cell, enter the index variety of the worth you need to return.

3. Use the CHOOSE perform to return the worth equivalent to the index quantity.

For instance, the next method returns the “Apple” worth from the desk in step 1:

“`
=CHOOSE(1, “Apple”, “Orange”, “Banana”)
“`

You too can use the CHOOSE perform to carry out a number of VLOOKUPs concurrently. For instance, the next method returns the values from the “Apple” and “Orange” rows of the desk in step 1:

“`
=CHOOSE({1,2}, “Apple”, “Orange”, “Banana”)
“`

Superior VLOOKUP Methods

10. Utilizing VLOOKUP with A number of Standards

VLOOKUP can be utilized with a number of standards to search out matches throughout a number of columns. To do that, create an array method utilizing the INDEX and MATCH capabilities. This is the syntax:

“`
=INDEX(vary, MATCH(criteria1, column_range1, 0) & MATCH(criteria2, column_range2, 0), column_number)
“`

For instance, to search out the gross sales for a particular product and area, you’d use the next method:

“`
=INDEX(sales_range, MATCH(product, product_range, 0) & MATCH(area, region_range, 0), 2)
“`

Concerns when utilizing VLOOKUP with a number of standards:

– Make sure that the standards ranges are sorted in ascending order.
– Use the precise match operator (0) within the MATCH perform.
– Use absolute references for the standards ranges to stop the method from altering when copied or stuffed down.

How To Do Vlookup In Excel With Two Spreadsheets

VLOOKUP is a robust Excel perform that lets you lookup knowledge in a desk primarily based on a specified worth. It may be used to retrieve knowledge from a single spreadsheet or from a number of spreadsheets. When utilizing VLOOKUP with two spreadsheets, there are a number of further steps that it is advisable to take to make sure that the perform works accurately.

First, it is advisable to guarantee that the info in each spreadsheets is in the identical format. Which means that the columns must be in the identical order and the info sorts must be the identical. If the info isn’t in the identical format, VLOOKUP won’t be able to search out the right values.

After you have ensured that the info is in the identical format, you should use the next steps to carry out a VLOOKUP with two spreadsheets:

  1. Within the cell the place you need to show the VLOOKUP consequence, kind the next method:

    2. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  2. The lookup_value is the worth that you simply need to lookup within the desk.
  3. The table_array is the vary of cells that comprises the info that you simply need to lookup.
  4. The col_index_num is the variety of the column within the table_array that comprises the worth that you simply need to return.
  5. The range_lookup is an non-obligatory argument that specifies whether or not you need a precise match or an approximate match. For those who omit this argument, VLOOKUP will carry out an approximate match.

For instance, the next method would lookup the worth in cell A2 of the spreadsheet named “Sheet1” within the table_array that’s outlined by the vary A1:D10 within the spreadsheet named “Sheet2”:

=VLOOKUP(A2, [Sheet2]!$A$1:$D$10, 2, FALSE)

Folks Additionally Ask

How do I hyperlink two spreadsheets in Excel utilizing VLOOKUP?

To hyperlink two spreadsheets in Excel utilizing VLOOKUP, you should use the next steps:

  1. Open each spreadsheets in Excel.
  2. Within the spreadsheet that you simply need to show the VLOOKUP outcomes, kind the next method within the cell the place you need to show the consequence:

    3. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  3. The lookup_value is the worth that you simply need to lookup within the desk.
  4. The table_array is the vary of cells that comprises the info that you simply need to lookup.
  5. The col_index_num is the variety of the column within the table_array that comprises the worth that you simply need to return.
  6. The range_lookup is an non-obligatory argument that specifies whether or not you need a precise match or an approximate match. For those who omit this argument, VLOOKUP will carry out an approximate match.
  7. Press Enter to show the VLOOKUP consequence.

Can I take advantage of VLOOKUP to lookup knowledge in a closed workbook?

Sure, you should use VLOOKUP to lookup knowledge in a closed workbook. To do that, it is advisable to use the next syntax:

=VLOOKUP(lookup_value, ‘[workbook_name.xlsx]sheet_name’!table_array, col_index_num, [range_lookup])

For instance, the next method would lookup the worth in cell A2 of the spreadsheet named “Sheet1” within the closed workbook named “Book1.xlsx”:

=VLOOKUP(A2, ‘[Book1.xlsx]Sheet1’!$A$1:$D$10, 2, FALSE)