Excel vlookup from another sheet

Hem / Teknik & Digitalt / Excel vlookup from another sheet

If the data is not sorted properly, you may end up having really strange results or a bunch of #N/A errors.

For our sample data, an approximate match Vlookup formula goes as follows:

And returns the following results:

  • For a lookup value of "80", "Cheetah" is returned because its speed (70) is the closest match that is smaller than the lookup value.
  • For a lookup value of "50", an exact match is returned (Lion).
  • For a lookup value of "30", a #N/A error is returned because the lookup value is less than the smallest value in the lookup column.


Video: How to VLOOKUP in Excel

Step-by-step guidance on how to create and use a VLOOKUP formula in Excel.

Special tools to Vlookup in Excel

Undoubtedly, VLOOKUP is one of the most powerful and useful Excel functions, but it's also one of the most confusing ones.

If two or more matches are found, the 1st one is returned. For multiple sheets, consider using INDEX/MATCH or other functions.

Summary of Steps

  1. Open both sheets.
  2. Identify your lookup value.
  3. Go to the sheet where you want the data.
  4. Select the cell for VLOOKUP.
  5. Enter the VLOOKUP formula.
  6. Reference the other sheet.
  7. Complete the formula.

Conclusion

Learning how to do a VLOOKUP in Excel between two sheets can significantly boost your productivity.

If an exact match is not found, the formula searches for the largest value that is smaller than the lookup value. This ensures VLOOKUP knows where to look for your data.

Step 7: Complete the Formula

Finish filling in the rest of the formula and press Enter.

Double-check everything before you hit Enter. Price_table in the below formula:

Table references, also called structured references, are resilient and immune to many data manipulations.

Though optional, the range_lookup parameter is highly important. We will try to explain the basics in a very plain language to make the learning curve for an inexperienced user as easy as possible. To distinguish the letter case, use case sensitive VLOOKUP formulas.

  • Remember about the importance of the last parameter.

    So far, so good, right?

  • The 3rd argument col_index_num is 2. For information about other errors, please see Why Excel VLOOKUP is not working.

    • Excel VLOOKUP examples

    I hope vertical lookup is starting to look a bit more familiar to you. For instance, you can remove or add new rows to your lookup table without worrying about updating the references.

    Using wildcards in VLOOKUP formula

    Like many other formulas, the Excel VLOOKUP function accepts the following wildcard characters:

    • Question mark (?) to match any single character.
    • Asterisk (*) to match any sequence of characters.

    Wildcards prove really useful in many situations:

    • When you do not remember the exact text you are looking for.
    • When you are looking for a text string that is part of the cell contents.
    • When a lookup column contains leading or trailing spaces.

      Please have a look at the below formula and try to "translate" it into English:

      • The 1st argument (lookup_value) clearly indicates that the formula looks up the word "lion".
      • The 2nd argument (table_array) is A2:B11. Once you do, the VLOOKUP will work its magic, and the result will pop up in the cell you selected.

        After you complete these steps, the cell will display the data from the other sheet that matches your lookup value.

        This ensures that we get the correct building for each team member and a #N/A error if for some reason the id is not found in the location table.

    In Excel, it is important to know how to transfer data from one worksheet to another using Vlookup, especially if you are working with multiple worksheets.

    Most often you will have to pull matching data from a different worksheet.

    To Vlookup from a different Excel sheet, put the worksheet's name followed by an exclamation mark in the table_array argument before the range reference. Requires sorting the lookup column in ascending order.

  • FALSE - exact match. If an exact match is not found, the #N/A error occurs.

    Excel VLOOKUP approximate match (TRUE)

    If range_lookup is set to TRUE or omitted (default), the formula looks up the closest match.

    Use TRUE for approximate match and FALSE for exact match. It makes the formula easy to read and manage.

    • ➤ Then, double click on the fill handle and apply the formula across column D.

    ➤ Column D should now display the transferred price data.

    3

    Alternatives to the VLOOKUP Function for Data Transfer Across Worksheets

    While the VLOOKUP function is widely used for transferring data across multiple worksheets, it has some major drawbacks.

    The counting starts from the leftmost column in the table array, which is 1.

  • Range_lookup (optional) - determines whether to search for approximate or exact match:
    • TRUE or omitted (default) - approximate match.

      excel vlookup from another sheet

      To our sample table, we have added one more column that ranks the animals by speed (column A) and want to find the 1st, 5th and 10th fastest sprinter in the world. Whether it’s learning about INDEX and MATCH or diving into pivot tables, there’s always more to discover.

      So, fire up Excel and start experimenting.

      The formula searches for a value exactly equal to the lookup value. How to do an exact match Vlookup

      To look up an exact match, just put FALSE in the last argument.

      For this example, let's take the animal speed table, swap the columns, and try to find the animals that can run 80, 50 and 30 miles per hour. Instead of manually searching for and cross-referencing data, you can let Excel do the heavy lifting.