Description:
This article describes how to get the column B value from Sheet 2 of an specific column A value from Sheet 1. Assuming columns A and B are paired properly in Sheet 2.
For example, we are provided with Sheet 1 and we need to know value of column B (?) for first cell of column A (1).
Sheet 1 | |
A | B |
1 | ? |
We also have Sheet 2 where column A values are paired with its respective column B values.
Sheet 2 | |
A | B |
1 | X |
2 | Y |
3 | Z |
Using the =VLOOKUP function in Excel we can easily get the B column value of A (in this case, column A value 1 has to column B value X).
This function is specially useful when you need to get the B value not of just one A field, but of many.
Required information:
List of values present in both Sheet 1 and Sheet 2 | In practice, IMEI or any other unique identifier compatible with ERP) |
Recommended tools:
Application | Notes |
Excel | Default in Windows. |
Allows you to copy formatted texts and paste them without format with a key bind of your choice. |
Scenario:
In this article we will pair IMEI numbers with their IMSI numbers assigned (SIM&RENT objects) as it is one of the most common use cases of this formula.
For this particular case we will use the browser version of Excel, but any will work.
Example:
We are provided with the following list of IMEI and we need to know their respective IMSI number.
860186054030859 |
860186054086703 |
860186054170986 |
860186054031295 |
860186054074477 |
Steps:
1- First of all, we need to get in ERP the spreadsheet where all IMEI are paired with their IMSI numbers for that particular client. See how: How to extract IMSI SIM numbers from ERP for a specific client
2- Open the resulting spreadsheet (named by default stock.production.lot.xls). We will rename the first sheet from default name Sheet 1 to Source for clarity sake:
3- Create a second sheet (named Sheet1 by default, we will rename it Target for clarity sake) and paste (without any format!) the list of IMEI provided by the client. Add headers to clarify data type (IMEI and IMSI):
4- If IMEI get displayed as "8,67035E+14" you can make them readable again by formatting the cells as Number and deleting decimals:
5- Now let's input the VLOOKUP formula in the first free cell of IMSI column (in this case, B2):
=VLOOKUP( the formula code
A2; value, usually a cell address, to look up in the source sheet. In this case, it is A2 the first cell with an IMEI number in Target sheet.
'Source'C:D; the area where both the lookup value and its associated field are located. In this case, columns C (IMEI) and D (IMSI) are selected as a whole. IMPORTANT: this area has to be format cleared as well beforehand.
2; from the columns previously selected in Source sheet, which one contains the values we are looking for. In this case it is 2, which corresponds to column D (IMSI), because we are looking for the IMSI numbers.
FALSE) always write false in this part.
In summary, the formula for the first cell will look like this:
=VLOOKUP(A2;Source!C:D;2;FALSE)
Type Enter and the looked up value, in this case the IMSI number, should appear :)
You can expand the first cell downwards to get the rest of values, the formula will adapt itself automatically:
This example is attached to this article (stock.production.lot.xlsx), plus an alternative explanation of VLOOKUP using only and excel spreadsheet (VLOOKUP.xlsx).