Vlookup from particular part of cells value – Excel Idea

Last updated on June 24, 2013 @ No comments yet

Vlookup is a powerful function of spreadsheet processing document. By providing exact lookup value we can find anything within array of cells from current sheet, different sheet or even sheet from other files including live or closed files.

But databases are sometime consists of an integrated set of codes. Many information are stored into a single code for example ID number which is contain codes of identification data, location, division, salary and many more. In a nutshell there are no fixed value to be given into vlookup, so you need to extract or split the value into several columns which will contain the exact lookup value for vlookup.

There are a small different between MS Excel and OpenOffice/LibreOffice Calc in term of use. MS Excel use True or False as value for range logical while OpenOffice/LibreOffice use 1 or 0 (one or zero) as the value for short order. Same function but different name and value.

Let say you have a list of employees data, for example ID numbers. The number contain of many information such as division code, expertise or maybe their birth dates. Based on their ID number, you want to make an employee summarize categorized by their division. Take a look on the following example.



The employees division code stored on the 5th position of the employees ID number and we want to fill the division column with the right values based on their ID number.


By using MID string function, we can extract the division code from employees ID number and then use it as a lookup value for VLOOUP function.


Example complete code :



1. Extract the 5th string from cell B2


2. Make sure that the value is a number


3. Use the extracted value to search in the division sheet its related division name


You can use LEFT or RIGHT function to extract string based o the string position and use it as a lookup value. Download for the example excel file here.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

Leave a Reply


Loading Google+ Comments ...
Loading Facebook Comments ...