Skip to main content

Using VLOOKUP and HLOOKUP with Real-Life Examples in Google Sheets

Using VLOOKUP and HLOOKUP with Real-Life Examples in Google Sheets

Using VLOOKUP and HLOOKUP with Real-Life Examples in Google Sheets

The VLOOKUP and HLOOKUP functions are powerful tools for searching and retrieving data from large datasets in Google Sheets. These functions allow you to quickly find information without scrolling through hundreds of rows or columns.

🔎 1. The VLOOKUP Function: Vertical Lookup

The VLOOKUP (Vertical Lookup) function is used to search for a value in the first column of a range and return a value from another column in the same row. The syntax is:

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

Here’s how to use VLOOKUP with a real-life example:

  • Example 1: You have a list of product IDs in column A and product prices in column B. To find the price of a product with ID "A123", you would use:
    • =VLOOKUP("A123", A1:B10, 2, FALSE)
  • This formula searches for "A123" in column A, and returns the corresponding price from column B.

🔄 2. The HLOOKUP Function: Horizontal Lookup

The HLOOKUP (Horizontal Lookup) function works similarly to VLOOKUP, but it searches for a value in the first row and returns a value from another row in the same column. The syntax is:

=HLOOKUP(search_key, range, index, [is_sorted])

Here’s a real-life example of HLOOKUP:

  • Example 2: You have a dataset where the first row contains months (January to December) and the second row contains monthly sales figures. To find the sales for June, you would use:
    • =HLOOKUP("June", A1:L2, 2, FALSE)
  • This formula searches for "June" in the first row, and returns the corresponding sales value from the second row.

📊 VLOOKUP vs HLOOKUP

While both VLOOKUP and HLOOKUP perform similar functions, the main difference is the direction in which they search:

  • VLOOKUP: Searches for a value in the first column (vertical lookup).
  • HLOOKUP: Searches for a value in the first row (horizontal lookup).

Choose the function based on whether your data is organized vertically (VLOOKUP) or horizontally (HLOOKUP).

💡 Tips for Using VLOOKUP and HLOOKUP

  • Exact Match: Always set the [is_sorted] argument to FALSE for an exact match. For example: =VLOOKUP("A123", A1:B10, 2, FALSE)
  • Column/Row Index: Be sure to enter the correct column index number (for VLOOKUP) or row index number (for HLOOKUP) to retrieve the correct data.
  • Using VLOOKUP Across Multiple Sheets: You can use VLOOKUP to search across multiple sheets by referencing the sheet name. For example: =VLOOKUP("A123", 'Sheet2'!A1:B10, 2, FALSE)

📥 Downloadable PDF

Tags: VLOOKUP function, HLOOKUP function, Google Sheets tutorial, lookup functions, VLOOKUP examples, Google Sheets formulas

Comments

Post a Comment

Popular posts from this blog

How to activate office 2016 using script ??

How to activate office 2016 using script ?? 1. Copy the following script in notepad and save as .bat . 2. Run the bat file as administrator. @echo off title Permanently Activate Office 365 ProPlus for FREE - Somethingos.blogspot.incom&cls&echo ============================================================================&echo #Project: Activating Microsoft software products without software&echo ============================================================================&echo.&echo #Supported products: Office 365 ProPlus (x86-x64)&echo.&echo.&(if exist "%ProgramFiles%\Microsoft Office\Office16\ospp.vbs" cd /d "%ProgramFiles%\Microsoft Office\Office16")&(if exist "%ProgramFiles(x86)%\Microsoft Office\Office16\ospp.vbs" cd /d "%ProgramFiles(x86)%\Microsoft Office\Office16")&(for /f %%x in ('dir /b ..\root\Licenses16\proplusvl_kms*.xrm-ms') do cscript ospp.vbs /inslic:"..\root\Licenses16...
2 comments
Read more

How to add or delete user in CMD??

How to add or delete user in CMD?? 1. Run CMD as Administrator . 2. Type following commands to add a use :           >net user test /add                >net user test /enable:yes         >net user test 123@test here test is a user and 123@test is password. 3. To delete :          >net user test /delete
Post a Comment
Read more

Activate windows 10 without software, Using .bat script

Here is a way to activate Windows 10 using Script. # Make sure you're connected with Internet. #  Open notepad,  paste the script save as file abc.bat and run as administrator. @echo off title Windows 10 &cls&echo ************************************ &echo Copyright: Sudhir: 2018  &echo.&echo Supported products:&echo - Windows 10 Home&echo - Windows 10 Professional&echo - Windows 10 Enterprise,&echo - Windows 10 Education&echo.&echo.&echo ************************************ &echo Windows 10 activation... cscript //nologo c:\windows\system32\slmgr.vbs /ipk TX9XD-98N7V-6WMQ6-BX7FG-H8Q99 >nul cscript //nologo c:\windows\system32\slmgr.vbs /ipk 3KHY7-WNT83-DGQKR-F7HPR-844BM >nul cscript //nologo c:\windows\system32\slmgr.vbs /ipk 7HNRX-D7KGG-3K4RQ-4WPJ4-YTDFH >nul cscript //nologo c:\windows\system32\slmgr.vbs /ipk PVMJN-6DFY6-9CCP6-7BKTT-D3WVR >nul cscript //nologo c:\windows\system32\slmgr.vbs /i...
Post a Comment
Read more