Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Data bars Examples in Excel
  • How to Create Thermometer Chart in Excel
  • Get column index in Excel Table
  • How to count table columns in Excel
  • How to Sort by Color in Excel

References

  • How to use Excel COLUMN Function
  • Two-column Lookup in Excel
  • How to retrieve first match between two ranges in Excel
  • How to use Excel VLOOKUP Function
  • How to create dynamic named range with OFFSET in Excel

Data Validations

  • Excel Data validation specific characters only
  • Excel Data validation date in specific year
  • Excel Data validation number multiple 100
  • Excel Data validation whole percentage only
  • Excel Data validation only dates between

Excel VLOOKUP and HLOOKUP functions Examples

by

Learn all about Excel’s lookup & reference functions such as the VLOOKUP, HLOOKUP, CHOOSE, MATCH and INDEX  function.

Navigation: Formula Tab → Function Library Group → Lookup and Reference

Vlookup

VLOOKUP stands for Vertical lookup.  The VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.

Syntax for VLOOKUP function:  VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

1. Insert the VLOOKUP function shown below.

Explanation

The VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc. Visit our page about the VLOOKUP function for much more information and many examples.

Hlookup

HLOOKUP stands for  Horizontal lookup.

Syntax for HLOOKUP function : HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Choose

The CHOOSE function returns a value from a list of values, based on a position number.

Syntax for CHOOSE function: CHOOSE(index_num, value1, [value2], …)

Explanation: Boat found at position 3.

Match

The MATCH function returns the position of a value in a given range.

Syntax for MATCH function: MATCH(lookup_value, lookup_array, [match_type])

Explanation: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found. Use INDEX and MATCH and become an Excel pro.

Index

The INDEX function below returns a specific value in a two-dimensional range.

Syntax for INDEX function: INDEX(array, row_num, [column_num])

Explanation: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

The INDEX function below returns a specific value in a one-dimensional range.

Explanation:

97 found at position 3 in the range E4:E7. .

Post navigation

Previous Post:

TRUE function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Pie Chart

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function
  • Nested IF function example in Excel

Date Time

  • Get month from date in Excel
  • Get last day of month in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • Get first day of previous month in Excel

Grouping

  • If cell contains one of many things in Excel
  • How to randomly assign data to groups in Excel
  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel

General

  • How to Create Calendar in Excel
  • How to calculate percent of students absent in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Basic numeric sort formula in Excel
  • Create dynamic workbook reference to another workbook in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning