vlookup and hlookup are formulas that allow to search a data in another sheet. Both work the same, the main difference is:
Formula 1 |
Formula 2 |
When I use the formula ?
When I need to display data of another or multiple different sheets into 1 single main resume sheet.
How to use the formula ?
The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".
For the formula to work, you need to have at least 1 same column for all sheets. For instance, you have 3 sheets:
For the sheet1 to display the “country” and “comment” data, you need to have in the sheet2 and in the sheet3 the column “number”. It doesn’t matter if “number” is in column A, B, C or Z, etc.
Sheet2 |
Sheet3 |
How are the formulas ?
=VLOOKUP()
=HLOOKUP()
Putting the formula in sheet1:
Formula 1 (if there is a space in the name of the sheet, use the 2nd one):
=VLOOKUP(B2,sheet2!A:C,2,0)
=VLOOKUP(B2,'comment data'!A:C,2,0)
Formula 2 (if there is a space in the name of the sheet, use the 2nd one):
=VLOOKUP(B2,sheet3!D:F,3,0)
=VLOOKUP(B2,'country data'!D:F,3,0)
I will take my previous example to explain:
As you can see, for all sheets, we have the column “number” as the main reference for vlookup to search, it doesn’t matter that “number” is in column A or in column Z. Depending on where is located the column “number”, the formula is different:
The most important is that the first letter is where is located the column “number”. In this example:
The second letter, C for sheet2 and F for sheet3, is doesn’t matter meanwhile the column is inside:
And to end my explanation, 2 for sheet2 and 3 for sheet3, is the numeric number:
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...