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 system in Excel that lets you lookup information from a desk based mostly on a particular worth. On this article, we’ll discover how one can use VLOOKUP in Excel with two spreadsheets, enabling you to seamlessly retrieve information from a number of sources.

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

After you have created your two spreadsheets, you need to use the VLOOKUP operate to retrieve information from the “Information” spreadsheet based mostly on the values within the “Lookup” spreadsheet. The syntax of the VLOOKUP operate 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 main spreadsheet that you simply wish to use to seek for matching values in your secondary spreadsheet. This worth will be any cell reference or vary of cells, and it should be of the identical information kind because the column you are looking out in your secondary spreadsheet.

**For instance:** Should you’re utilizing VLOOKUP to seek out the value of a product based mostly on its product code, your lookup worth could be the product code cell in your main spreadsheet.

Listed below are some essential concerns when deciding on your lookup worth:

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

2. **Uniqueness:** If attainable, the lookup worth ought to be distinctive. It will be sure that VLOOKUP returns the right matching worth. If the lookup worth shouldn’t be distinctive, VLOOKUP might return a number of matching values, which will be complicated.

3. **Information Kind:** The lookup worth should be of the identical information kind because the column you are looking out in your secondary spreadsheet. If the information sorts don’t match, VLOOKUP won’t be able to seek out the matching values.

Coming into the VLOOKUP Formulation

To enter the VLOOKUP system, observe these steps:

1. Choose the cell the place you need the end result to look.
2. Kind the next system: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
3. Substitute lookup_value with the worth you wish to lookup.
4. Substitute table_array with the vary of cells containing the information you wish to search.
5. Substitute col_index_num with the column quantity containing the worth you wish to return.

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

Instance

Suppose you will have a desk of worker information 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 Assist

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

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

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

Utilizing CHOOSE for Conditional VLOOKUP

The CHOOSE operate can be utilized to carry out a conditional VLOOKUP by specifying a spread of lookup values and a corresponding vary of outcomes. The syntax of the CHOOSE operate 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, similar to the index numbers 1, 2, …, N.

To make use of CHOOSE for conditional VLOOKUP, observe 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 wish to return.

3. Use the CHOOSE operate to return the worth similar to the index quantity.

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

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

You can even use the CHOOSE operate to carry out a number of VLOOKUPs concurrently. For instance, the next system 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 seek out matches throughout a number of columns. To do that, create an array system utilizing the INDEX and MATCH features. Here is the syntax:

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

For instance, to seek out the gross sales for a particular product and area, you’ll use the next system:

“`
=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 factors ranges are sorted in ascending order.
– Use the precise match operator (0) within the MATCH operate.
– Use absolute references for the factors ranges to stop the system from altering when copied or stuffed down.

How To Do Vlookup In Excel With Two Spreadsheets

VLOOKUP is a robust Excel operate that lets you lookup information in a desk based mostly on a specified worth. It may be used to retrieve information from a single spreadsheet or from a number of spreadsheets. When utilizing VLOOKUP with two spreadsheets, there are a number of extra steps that you’ll want to take to make sure that the operate works appropriately.

First, you’ll want to guarantee that the information in each spreadsheets is in the identical format. Which means the columns ought to be in the identical order and the information sorts ought to be the identical. If the information shouldn’t be in the identical format, VLOOKUP won’t be able to seek out the right values.

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

  1. Within the cell the place you wish to show the VLOOKUP end result, kind the next system:

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

  2. The lookup_value is the worth that you simply wish to lookup within the desk.
  3. The table_array is the vary of cells that accommodates the information that you simply wish to lookup.
  4. The col_index_num is the variety of the column within the table_array that accommodates the worth that you simply wish to return.
  5. The range_lookup is an elective argument that specifies whether or not you need a precise match or an approximate match. Should you omit this argument, VLOOKUP will carry out an approximate match.

For instance, the next system 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)

Individuals Additionally Ask

How do I hyperlink two spreadsheets in Excel utilizing VLOOKUP?

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

  1. Open each spreadsheets in Excel.
  2. Within the spreadsheet that you simply wish to show the VLOOKUP outcomes, kind the next system within the cell the place you wish to show the end result:

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

  3. The lookup_value is the worth that you simply wish to lookup within the desk.
  4. The table_array is the vary of cells that accommodates the information that you simply wish to lookup.
  5. The col_index_num is the variety of the column within the table_array that accommodates the worth that you simply wish to return.
  6. The range_lookup is an elective argument that specifies whether or not you need a precise match or an approximate match. Should you omit this argument, VLOOKUP will carry out an approximate match.
  7. Press Enter to show the VLOOKUP end result.

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

Sure, you need to use VLOOKUP to lookup information in a closed workbook. To do that, you’ll want to use the next syntax:

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

For instance, the next system 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)