Advanced Excel Formulas Combining LEFT, SEARCH and VLOOKUP
This workbook contains two worksheets. The first worksheet("Hires") has a record of the equipment hires that take place within an organisation. The code indicating which item was hired is embedded in the hire reference in column A.
The second sheet ("Items") contains a lookup table detailing the piece of equipment indicated by each code.
Column A of the "Hires" table shows the Hire Ref(erence); but the Hire Ref has three components, separated by hyphens; and it is only the first of these components that matches column A of the "Items" worksheet. So, we need to come up with a formula which will extract all the text before the first hyphen.
The LEFT function takes two parameters: firstly, the text from which you want to extract a given number of characters; and this, of course, is the text in the cell A2.
The second argument is the number of characters to be extracted. However, in this example, we cannot simply provide a literal number. We cannot say three or four, because the number varies.
www.GComSolutions.co.uk
So, instead, we use the SEARCH function to calculate this number, as shown in the diagram on the left.
The SEARCH function becomes the second argument of the LEFT function: num_chars. The SEARCH function, in turn, takes two arguments. Firstly, we have the find_text; the text for which you are searching. And, secondly, the within_text; the cell in which you are looking for that text.
www.GComSolutions.co.uk
The SEARCH function returns a number, which is the character position of the string for which you are searching. In this case, in cell A2, the hyphen is in character position five, but of course we do not want to extract five characters. We always want to extract one character fewer than the character position of the hyphen Therefore, after the closing parenthesis which ends the SEARCH function, we need to insert: "-1". www.GComSolutions.co.uk
Splitting the formula onto several lines makes it easier to read. We can also increase the size of the formula bar by clicking the expand button (the down-arrow on the right of the formula bar).
www.GComSolutions.co.uk
We begin by inserting: "=LEFT("; and then we press Alt-Enter to move to a new line within the formula bar and use the spacebar to indent the next line under the opening parenthesis following the LEFT function.
Now, we insert the parameters (arguments) of the LEFT function. The first parameter is the text which, as you can see, is the text in the adjacent column A. Since our formula is in row 2 of the worksheet, we therefore click on A2 and Excel inserts the cell reference into our formula.
www.GComSolutions.co.uk
Now, onto the second argument. Enter a comma, press Alt-Enter and then press the spacebar to indent the second argument of the LEFT function underneath the first. For our second argument, we now need to use a function which will identify the character position of the first hyphen.
www.GComSolutions.co.uk
Excel provides two candidates SEARCH and FIND.
FIND is case sensitive, and SEARCH is not. For that reason, it is probably best to think of SEARCH as the standard function and then only use FIND on those occasions where you need to take the case into account.
www.GComSolutions.co.uk
Since the SEARCH function does not have any other functions nested inside it, we write the function and its arguments on a single line.
Once we have found the position of the hyphen, we need to subtract one. Thus, for example, in cell A2, the hyphen is in position five. Therefore, we do not want to extract the first five characters; we want to extract only the first four. So, we need to subtract one from the figure returned by the SEARCH function.
www.GComSolutions.co.uk
Thus, the second argument of the LEFT function should read: “SEARCH("-", A2) - 1”.
We can now highlight and copy the entire formula which we have just completed (apart from the equal sign).
Then press Escape to avoid accidentally modifying the formula. We can now paste this formula as the first argument of the VLOOKUP function which we will use in cell C2 to find the name of the item hired. www.GComSolutions.co.uk
VLOOKUP requires four arguments: • Firstly, we have the lookup_value; the item for which we are seeking a match.
• The second argument of the VLOOKUP function is the table_array, the columns in which we are looking for a value. • The third argument of VLOOKUP is the column_index number within the lookup table (which column contains the answer we are seeking). • The fourth, argument of VLOOKUP is the range_look_up, which specifies whether you are looking for an exact match or an approximate one.
www.GComSolutions.co.uk
The first argument of VLOOKUP is the lookup_value; the item for which we are seeking a match. In this case, this is the code which will be extracted by our LEFTSEARCH formula. So we simply paste in the formula we copied and use the spacebar to indent the arguments further in, to indicate that they now constitute the first argument of the VLOOKUP function. www.GComSolutions.co.uk
The second argument of the VLOOKUP function is the table_array, the columns in which we are looking for a value.
We will be looking in the adjacent worksheet: “Items”. So, we activate the “Items” worksheet and drag across column headings A and B, to create the cell reference: “Items!A:B”.
www.GComSolutions.co.uk
The third argument of VLOOKUP is the column_index number within the lookup table (which column contains the answer we are seeking); and it is always numeric. So, we type a comma; Press AltEnter; and then use the spacebar to align the cursor under the first and second arguments of VLOOKUP. Then we simply enter the number two. www.GComSolutions.co.uk
The fourth, and final, argument of VLOOKUP is the range_look_up, which specifies whether you are looking for an exact match or an approximate one. We are looking for an exact match. So, we type a comma; Press AltEnter; and then use the spacebar to align the cursor under the first, second and third arguments of VLOOKUP.
www.GComSolutions.co.uk
Then we simply enter either FALSE or zero.
Finally, we press Alt-Enter; use the spacebar to align the cursor under the opening parenthesis which follows the VLOOKUP function and insert the closing parenthesis to complete the formula. We can then copy this formula down into the cells below.
www.GComSolutions.co.uk
This is an example of how one (nested) function can serve as the argument of another (parent) function. In this example, the SEARCH function is nested as the second (num_chars) argument of the LEFT function; which, in turn, was nested as the first (lookup_value) argument of the VLOOKUP function.
www.GComSolutions.co.uk
Writing the formula on multiple lines; and indenting the lines, emphasizes the hierarchical relationship between the various elements.