oxlogoboss

• The Vlookup Book Pdf Chandoo Project Online
• The Vlookup Book Pdf Chandoo Project Free

Download the Book:101 Ready-To-Use Excel Formulas PDF For Free, Preface: Mr. Spreadsheet has done it again with 101 easy-to-apply Excel formulas.

Related Articles

• 1 Use an Alphanumeric Mix in VLOOKUP Statements in Excel
• 2 Use VLookup With Different Sheets on Excel
• 3 Do Proportions on Excel
• 4 Get a Date Using VLOOKUP in MS Excel 2003

VLOOKUP and Search are two functions that Excel uses to search for text. VLOOKUP finds data in a column and returns the contents of an adjacent cell. Search finds data in a cell and returns the string's position in the larger string. A formula might combine the functions. For example, suppose your inventory spreadsheet uses two types of product code in a string related to the product. The two functions can together find the relevant string and locate it in the code, determining which type of code each product uses.

Identify the Column with Relevant Data

Identify the column whose data you want Excel to find in a different array. With this example, this column will list product codes. The different array will list product codes along with strings of data that form the code.

Insert the VLOOKUP Code

Insert the following code to the right of the uppermost cell in the column from Step 1:

=Search('(=VLOOKUP(A1, F10:I20, 3, FALSE))', A1, 1)

Replace the Instances of 'A1'

Replace both instances of 'A1' in the formula with the cell reference for the data you want Excel to find in the array.

The Vlookup Book Pdf Chandoo Project Online

Replace 'F10' with Relevant Data

Replace 'F10' with the top cell from the array's first column.

Replace 'I10' with Relevant Data

Replace 'I10' with the bottom cell from the array's last column.

Identify the Column to Search

Identify the column in the array that contains the data you want the Search function to find. With this example, suppose that the array's third column contains products' category codes, which are strings that serve as components for the product code.

Identify the Column Reference Index

Identify the column reference index for the column from Step 6. This value describes the column's position with respect to the first column in the array, which VLOOKUP searches. If the column is the second column in the array, the column reference number is '2.' If the column is the third column in the array, the column reference number is '3.'

Replace '3' with Column Reference Number

Replace '3' in the formula with the column reference number you identified in Step 7.

Identify Position for Search Function

Identify the position in the string from which you want the Search function to run. If you want Search to look at the entire string, as is most often the case, this position is '1.' If you want Search to ignore the first letter and start searching from the second letter, this position is '2.'

The Vlookup Book Pdf Chandoo Project Free

Replace '2' with Position from Search Function

Replace '2' in the formula with the position from Step 9.

Press the Enter Key

Press the Enter key. The formula runs, using both functions. With this example, the formula finds a product code in the array. VLOOKUP finds the inventory code associated with the product. Search finds the inventory code's position in the product code.

About the Author

Ryan Menezes is a professional writer and blogger. He has a Bachelor of Science in journalism from Boston University and has written for the American Civil Liberties Union, the marketing firm InSegment and the project management service Assembla. He is also a member of Mensa and the American Parliamentary Debate Association.

Choose Citation Style