Vlookup and Hlookup Unearthed – Google Doc Spreadsheet

0
524
Vlookup and Hlookup

All spreadsheet users are proud to know the use of Vlookup and Hlookup, if they use spreadsheet for serious data entry. For many spreadsheet users, it’s just a place to make formats, some basic calculations etc. For people who wish to see Google Sheets in a different way, I have already covered some advanced Google Doc Spreadsheet tutorials. Go through that tutorials and be an advanced spreadsheet user.

Here in this Google doc spreadsheet tutorial, we can learn how to use Vlookup and Hlookup in Google Sheets. I give importance to Vlookup in this post. You may ask why Vlookup? The answer, I found Vlookup more useful for my data entry needs.

Here you can also learn to use Hlookup. Just understand first how to use Vlookup in Google Doc Spreadsheet. Then similarly you can use Hlookup in Google Doc spreadsheet.

Vlookup and Hlookup

As usual I tried to made this tutorial as simple as I can. Learn the use of Vlookup in practical use. Just follow this tutorial and you will know the power of Vlookup and Hlookup functions.

How to Use Vlookup and Hlookup in Google Sheets – Examples

First let us learn how to practically use Vlookup in Google Doc Spreadsheet (It’s same as in Microsoft Excel)

What is Vlookup?

Simply speaking, Vloookup means vertically look up for something and bring corresponding details.

You can use Vlookup to Search for a value in the first column of a table. It returns a value in the same row based on the column number that you specified in the function.

What is Hlookup?

Hlookup can be easily learn once you understand Vlookup. It’s the horizontal look up in a table.

Vlookup Sytanx

Here is the formula syntax of Vlookup in Google Doc Spreadsheet:

VLOOKUP(search_key, range, index, [is_sorted])

Example to Use Vlookup Function in Google Sheets

Before explaining the formula, just type the below data in a Google Doc Spreadsheet as it’s.

Don’t forget to put the values in the same cells as per the data provided below. Example to How to use VLOOKUP in Google Doc Spreadsheet From the above database you can quickly find details about an item based on item code. If you use Vlookup to look up for item code, you can get details corresponding to that item code.

Note: The above formula is one simple example to the advanced use of Vlookup in Google Sheets. I made a calculation with the Vlookup above.

See the formula applied above. Check the formula bar above which is yellow highlighted. You can see that I used Vlookup to look for an item code “W-240”. The formula I applied is like below.

=VLOOKUP(“W-240”,A2:D6,3,False)*(1+VLOOKUP(“W-240”,A2:D6,4,false))

In the formula the first part is the normal vlookup function. I made one calculation with the rest.

Once again look at the syntax below.

VLOOKUP(search_key, range, index, [is_sorted])

Details of the Vlookup Formula Used

The formula, not the syntax, above searches down the first column of a range for a search_key, i.e. “W-240” here and returns the value of a specified cell, here column “C4”, third column, in the row found in Vlookup searches.

Note: W-240 is the search_key. It can be a text or cell address. Cell address can be a drop down list item.

Here range means, A2:D6, the range to consider for the search. Its’ the entire database normally.

In the above formula, number 3 is the column index of the value to be returned, where the first column in ‘range’ is considered as 1.

Put “True” if the column to be searched (the first column of the specified range) is sorted, else put “False”. This’ is important otherwise the result will be wrong. That’s all.

As told above I have additionally used another Vlookup function in the formula above. In the above example the markup value of W-240 is 26%. Check the sample data above and I hope you have already the sample data in a spreadsheet. Otherwise you cannot learn this. We need to add this 26% to the unit price to get the retail value.

So what I applied is added 26% to the look up value 700.00. Means 700.00×126%. Means; =VLOOKUP(“W-240”,A2:D6,3,False) = 700 (1+VLOOKUP(“W-240”,A2:D6,4,false)) = 126% i.e., 700×126% = 882.00, that’s the result we got. Hope you understand the advanced use of Vlookup in Google Doc spreadsheet.

Example to Use Hlookup Function in Google Sheets

Now, you can learn how to use Hlookup.

First copy and paste the above data as follows using the transpose command in Google Doc Spreadsheet or directly type it.

advanced use of HLOOKUP Google Doc Spreadsheet Horizontal look up Searches across the first row of a range for a key, i.e. “W-240” here,  and returns the value of a specified cell in the column found. See the formula bar above and understand the concept as it’s similar to Vlookup.

Similar: Index Match Combo – A Better Alternative to Vlookup and Hlookup

Hope you understand the use of Vlookup and Hlookup in Google Doc Spreadsheet. It’s applicable to Microsoft Excel also.

LEAVE A REPLY

Please enter your comment!
Please enter your name here