Buzz
Combining functions in Excel enhances computational efficiency and accuracy significantly. Vlookup is a condition-based lookup function, while Left function extracts characters from the left side of a string. Combining Vlookup and Left functions enables Vlookup to perform precise and swift lookups.
GUIDE ON UTILIZING VLOOKUP WITH LEFT FUNCTION
Syntax and Usage of Vlookup and Left Functions
Vlookup Function: It's a lookup function that returns results based on vertical lookup.
- Syntax: =Vlookup(lookup_value, table_array, col_index_num, [range_lookup])
Where:
+ lookup_value: Value used for searching
+ table_array: Data range to search within
+ col_index_num: Order of the column to retrieve data from the lookup table.
+ range_lookup: Search scope, TRUE equals 1 (relative search), FALSE equals 0 (exact search).
Note: For the lookup_value, press F4 3 times, for the table_array, press F4 once.
Significance of Using F4
- F4 (1 time): for absolute value. Absolute value means fixing both column and row ⇒ $column$row
Example: $A$8 ⇒ fix column A and fix row 8
- F4 (2 times): for relative column and absolute row value - Understood as fixing the row, not the column ⇒ column$row
Example: A$8 ⇒ fix row 8, not fix column A
- F4 (3 times): for relative row and absolute column value - Understood as fixing the column, not the row ⇒ $column$row
Left Function: It's a function used to extract characters from the left side of a string.
- Syntax:LEFT(text,n)
Where:
+ Text: string of characters to be cut
+ n: number of characters to cut
Let's delve into examples to better understand these two Functions and how to combine them.
We have a data table as shown below
We will use LEFT function to extract Industry Code from the Class column.
Step 1: Fill formula =LEFT(B6,2) into cell C6. The formula means cutting 2 characters from cell B6.
Step 2: Then press Enter. The result will show as 2 characters CN cut out from the string CNTT1.
Step 3: Drag from cell C6 downward to automatically fill formulas and get corresponding results in the cells below.
Step 4: Fill formula =VLOOKUP($C6,$G$6:$H$10,2,0). $C6 is the Value used for searching, ,$G$6:$H$10 is the Data range to search within, 2 is the Order of the column to retrieve data from the Data range to search within, 0 is the Search scope, which is exact.
Step 5: Then press Enter. The result will show the corresponding Industry Name for the Industry Code. Drag downward again to automatically fill formulas and get corresponding results.
Step 6: Fill formula =VLOOKUP(LEFT(B6,2),$G$6:$H$10,2,0). We replace the Value used for searching from $C6 to LEFT(B6,2).
Step 7: Then press Enter and do the same as in step 5.
Above is the guidance on how to combine Vlookup function with Left function. Hopefully, after this article, you can easily utilize the combination of Vlookup and Left functions for your convenience. If you encounter any difficulties during implementation, feel free to comment below, and Mytour's technical team will assist you.
In addition to combining with Left function, Vlookup can also be combined with IF function to solve various problems. You can combine Vlookup and IF to search for conditional data, details on how to combine Vlookup and IF here.
STOCKHISTORY function is one of the most crucial functions in Excel. You can refer to the article STOCKHISTORY function to effectively use this function.
