The VLOOKUP function is a powerful tool in Excel

 

The VLOOKUP function is a powerful tool in Excel

VLOOKUP Syntax

Excel

Copy code

=VLOOKUP(lookup value, table array, col index num, [range lookup])

Arguments

  • lookup value: The value you want to search for. This can be a value, a cell reference, or a text string.
  • table array: The range of cells that contains the data. The first column of this range is where Excel will search for the lookup value.
  • col index num: The column number in the table array from which to retrieve the value. The first column of the table array is 1, the second column is 2, and so on.
  • range lookup: An optional argument that determines whether you want an exact match or an approximate match:

    • TRUE or omitted: Finds an approximate match. If an exact match is not found, the next largest value less than the lookup value is returned. The first column of the table array must be sorted in ascending order.
    • FALSE: Finds an exact match. If an exact match is not found, the function returns #N/A.

Example of VLOOKUP

Imagine you have a table of data like this:


YouTube channel:- https://youtube.com/@jemal_knowledge32?si=_4a2dJtf5DFl3iA2

YouTube video:- https://youtu.be/ZJukF2guPYYsi=S6R5HLnvBZgtUSDb

Basic VLOOKUP Example

You want to find the price of the product with ID 102.

  1. lookup value: H4
  2. table array: B3:F13

(assuming your data starts from cell B3:F13)

  1. col index num: 3 (since Price is in the third column of your table array)
  2. range lookup: FALSE (you want an exact match)

The formula would be:

Excel

Copy code

=VLOOKUP(H4,B3:F13,3, FALSE)

This formula searches for the value 102 in the first column of the range B3:F13

and returns the value from the third column in the same row, which is GHR.MATT.

Using Cell References

You can also use cell references for the lookup value. If the value H4 is in cell B7, the formula would be:

Excel

Copy code

=VLOOKUP(H5,B3:F13,3, FALSE)



Approximate Match

If you want to find an approximate match (useful for looking up ranges of values), you can set the range lookup to TRUE or omit it. For example, if your table array is sorted in ascending order by Product ID and you want to find the price for the nearest match to PUNCH.MATT, you can use:

Excel

Copy code

=VLOOKUP(H5,B3:F13,3, FALSE)    

This will return PUNCH.MATT since OPAL GREY is the largest value less than OPAL GREY.0.

Instagram:- https://www.instagram.com/jemal_knowledge?igsh=ZXVhcTFrZ2V2ZmVh

Common Errors

  1. #N/A: Occurs when the function cannot find the lookup value in the first column of the table array.
  2. #REF!: Occurs when the col index num is greater than the number of columns in the table array.
  3. #VALUE!: Occurs if the col index num is less than 1.

Tips

  • Ensure the first column of the table array is sorted in ascending order if using an approximate match (TRUE or omitted).
  • Use FALSE for range lookup if you need an exact match.
  • Make sure your table array covers all columns you might reference with col index num.

Comments

Post a Comment

Popular posts from this blog

Current Date and Time for Excel Formulas and Functions 2024 in Hindi and Gujarati | Excel Formula Tutorial | Excel Formulas

Image
  Excel Formulas for Current Date and Time Excel provides easy-to-use formulas for inserting the current date, time, or both into a worksheet. Here are the formulas along with examples: 1. Current Time Only Formula Formula: =TEXT(NOW(), "HH:MM:SS AM/PM") or =TEXT(NOW(), "HH:MM:SS") for a 24-hour format. Automatic Updates: Both =TODAY() and =NOW() are dynamic and update automatically whenever the worksheet recalculates. Static Date and Time: If you want to insert the current date or time as a static value (that doesn’t change), you can press Ctrl + ; for the current date and Ctrl + Shift + ; for the current time. Description: This formula extracts and formats the current time from the NOW() function. Example: If the current time is 11:55 AM, entering =  Ctrl + Shift + ;  press  will display 11:55:00 AM. https://youtu.be/8sM9GWmZHGU 2. Current Date Formula Formula: =TODAY() Description: This formula returns the current date. The date is updated every time th...
Read more
  Microsoft excel basic tools knowledge 1: Introduction to Excel Interface 1.1 Excel Interface Overview Image: Excel home screen with main sections labeled (Ribbon, Workbook, Worksheet, Formula Bar, etc.) Details: Brief description of each section. 1.2 Ribbon and Tabs Home Tab: Font Group: Image: Font group highlighted. Details: Changing font style, size, color, and adding effects (bold, italic, underline). Alignment Group: Image: Alignment group highlighted. Details: Aligning text (left, center, right), merging cells, wrapping text. Number Group: Image: Number group highlighted. Details: Formatting numbers, currency, dates, percentages. 2: Basic Functions and Formulas 2.1 Basic Formulas SUM Function: Image: Example of using the SUM function. Details: Adding a range of cells (e.g., =SUM(A1:A10) ). AVERAGE Function: Image: Example of using the AVERAGE function. Details: Calculating the average of a range of cells (e.g., =AVERAGE(B1:B10) ). COUNT Function: Image: Example...
Read more

COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS formulas in Excel

Image
  Excel Count Series Here’s a breakdown of the COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS formulas in Excel, including their usage and examples 1. COUNT Formula: The COUNT formula is used to count the number of cells that contain numbers within a specified range. COUNT: Counts the number of cells with numbers. COUNT: =COUNT(F2:F21) http://www.youtube.com/@Jemal_Knowledge32 https://youtu.be/Xi6Q8ayeuNs https://www.linkedin.com/posts/hitesh-darji-11452b271_exceltutorial-exceltips-exceltraining-activity-7224774354536996864-fpWC?utm_source=share&utm_medium=member_android https://www.instagram.com/reel/C-IUSn9yMR3/?igsh=MWg3YmZybXZ0bWNuMg== COUNTA:=COUNT(C2:D13)   3. COUNTBLANK Formula The COUNTBLANK formula is used to count the number of empty cells within a specified range. COUNTBLANK: Counts the number of empty cells. COUNTBLANK: = COUNTBLANK(B1:I21) 4. COUNTIF Formula The COUNTIF formula counts the number of cells that meet a single specifi...
Read more