Dear friends, I know you will have a few questions in your mind for VLOOKUP like what is VLOOKUP? , How do we use VLOOKUP in Excel? and, How do we do VLOOKUP in excel from another sheet in Excel? etc..
Here you will find your answer from below Tutorial.
VLOOKUP: VLOOKUP is an Excel function to retrieve data from a specific column in the table. The “V” stands for “vertical”. The value for which we need to retrieve data from table must appear in the first column of the table, with lookup columns to the right.
For using VLOOKUP it is required to have a lookup table with lookup values in the leftmost column. The corresponding result values can appear in any column towards the right of lookup values.
VLOOKUP Formula Syntax:
Lookup_value: The value to look for in the first column of a table.
Table_array: The table from which to retrieve a value.
Col_index_num: The column in the table from which a value need to retrieve.
Range_lookup: It is optional,
FALSE= exact match
VLOOKUP in Excel with an example:
In the above tutorial, we need to retrieve a value of salesperson from column C. For that we use VLOOKUP in Cell G5. By using VLOOKUP we can find a value for the name entered in F5. It is a very useful formula through which we can find specific value from the large table.
Buddy, VLOOKUP only looks right!
If VLOOKUP cannot find a match it returns a #N/A error.
Value of G5 is not available in the lookup column, the result is an error as #N/A.
Due to #N/A error in one cell Total sales value also results with error #N/A.
IFNA in conjunction with VLOOKUP
We can use IFNA in conjunction with VLOOKUP to replace the error #N/A with desired value or user-friendly message.
IFNA along with VLOOKUP is used to replace #N/A error with the specified value. This specified value can be treated as a Zero.
Multiple lookup tables
There can be multiple lookup tables instead of one. We can use IF Function to check whether a condition is met, and return a one lookup table if TRUE and another lookup if FALSE.
Step 1: Create two named ranges:
Table 2: UAE_MARKET
Step 2: Use the VLOOKUP Function in cell E2.
The bonus of salesperson depends on the market (UK or USA) and Achieved sales target. IF B2=USA, VLOOKUP Function uses Table 1 named USA_MARKET, if B2=UK, VLOOKUP Function used Table 2 named UK_MARKET.We need to set forth argument of VLOOKUP function to TRUE as an approximate match.
Similarly to find value from the raw we can use HLOOKUP.