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 strong system in Excel that permits you to lookup information from a desk based mostly on a selected worth. On this article, we are going to discover the best way to use VLOOKUP in Excel with two spreadsheets, enabling you to seamlessly retrieve information from a number of sources.

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

Upon getting created your two spreadsheets, you should utilize the VLOOKUP perform to retrieve information from the “Information” spreadsheet based mostly 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 main spreadsheet that you simply need to use to seek for matching values in your secondary spreadsheet. This worth may 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:** When you’re utilizing VLOOKUP to seek out the value of a product based mostly on its product code, your lookup worth can be the product code cell in your main spreadsheet.

Listed here are some vital concerns when choosing your lookup worth:

Issues
1. **Consistency:** The lookup worth should be constant throughout each spreadsheets. If the lookup worth is just not constant, VLOOKUP will be unable to seek out the matching values.

2. **Uniqueness:** If attainable, the lookup worth must be distinctive. This can be certain that VLOOKUP returns the proper matching worth. If the lookup worth is just not distinctive, VLOOKUP might return a number of matching values, which may 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 will be unable to seek out the matching values.

Getting into the VLOOKUP System

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. Exchange lookup_value with the worth you need to lookup.
4. Exchange table_array with the vary of cells containing the information you need to search.
5. Exchange col_index_num with the column quantity containing the worth you need to return.

6. Non-obligatory: Exchange [range_lookup] with FALSE for an actual match or TRUE for an approximate match. If omitted, TRUE is assumed.

Instance

Suppose you’ve gotten a desk of worker information within the vary A1:D5:

Emp ID Title 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 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 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, 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 need to return.

3. Use the CHOOSE perform 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”)
“`

It’s also possible to use the CHOOSE perform 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 Strategies

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 selected product and area, you’d use the next system:

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

Issues when utilizing VLOOKUP with a number of standards:

– Be certain that the factors ranges are sorted in ascending order.
– Use the precise match operator (0) within the MATCH perform.
– Use absolute references for the factors ranges to forestall the system from altering when copied or stuffed down.

How To Do Vlookup In Excel With Two Spreadsheets

VLOOKUP is a strong Excel perform that permits you to 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 just a few further steps that you must take to make sure that the perform works accurately.

First, you must make it possible for the information in each spreadsheets is in the identical format. Because of this the columns must be in the identical order and the information sorts must be the identical. If the information is just not in the identical format, VLOOKUP will be unable to seek out the proper values.

Upon getting ensured that the information is in the identical format, you should utilize the next steps to carry out a VLOOKUP with two spreadsheets:

  1. Within the cell the place you need 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 need to lookup within the desk.
  3. The table_array is the vary of cells that accommodates the information that you simply need to lookup.
  4. The col_index_num is the variety of the column within the table_array that accommodates the worth that you simply need to return.
  5. The range_lookup is an non-compulsory argument that specifies whether or not you need an actual match or an approximate match. When 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)

Folks Additionally Ask

How do I hyperlink two spreadsheets in Excel utilizing VLOOKUP?

To hyperlink two spreadsheets in Excel utilizing VLOOKUP, you should utilize 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 system within the cell the place you need 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 need to lookup within the desk.
  4. The table_array is the vary of cells that accommodates the information that you simply need to lookup.
  5. The col_index_num is the variety of the column within the table_array that accommodates the worth that you simply need to return.
  6. The range_lookup is an non-compulsory argument that specifies whether or not you need an actual match or an approximate match. When you omit this argument, VLOOKUP will carry out an approximate match.
  7. Press Enter to show the VLOOKUP end result.

Can I exploit VLOOKUP to lookup information in a closed workbook?

Sure, you should utilize VLOOKUP to lookup information in a closed workbook. To do that, you must 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)