Buzz
Use VLOOKUP function to retrieve information from a subsidiary table based on conditions from the main table to populate a specific column in the main table if the conditions are met. Here's how to use VLOOKUP function to get data from this sheet to another sheet.
Utilize VLOOKUP function to extract data from this sheet to another sheet
General Formula
=VLOOKUP(lookup,sheet!range,column,match)
Use VLOOKUP function to fetch data from this sheet to another sheet similar to using VLOOKUP function on the same sheet.
In the example displayed, the formula in cell F5 is:
=VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0)
Here, the VLOOKUP function retrieves data for each employee on Sheet2 and inserts it into the table on Sheet1.
How does this formula work?
Below is the list of office building numbers for employees located on Sheet2 in the example:
Use the formula below to retrieve the list of office building numbers for employees located on Sheet2 and bring them into Sheet1:
=VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0)
For the lookup value, utilize the value in column B as the employee ID.
For the table_array, utilize the data range B54:$C$104 along with the Sheet name:
Sheet2!$B$5:$C$104 // including sheet name
The only difference from the standard VLOOKUP formula is the inclusion of the Sheet name, informing the VLOOKUP function to use the data range on which Sheet.
Finally, the column number is 2, as the building numbers are displayed in the second column, and the VLOOKUP function is set to match exact data by adding the number 0 as the fourth argument.
This ensures that the result you receive matches the address of the building where the employee works, and if an #N/A error occurs, it may be because the employee ID was not found in the table. You can refer to Mytour's guide on fixing #N/A errors to troubleshoot on your own.
So, you've successfully utilized the VLOOKUP function to retrieve data from one sheet to another. The versatility of the VLOOKUP function is quite extensive; you can combine it with various other functions for data searching and retrieval. To quickly familiarize yourself with VLOOKUP, you can download a sample VLOOKUP function.
