Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel COLUMN Function
  • Basic INDEX MATCH approximate in Excel
  • Left Lookup in Excel
  • Count rows that contain specific values in Excel

Data Analysis Examples

  • Move chart to a separate worksheet in Excel
  • How to count table rows in Excel
  • Chart Axes in Excel
  • Filter Data Based on Date in Excel
  • How to Create Gantt Chart in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • How To Create Drop-down List in Excel
  • Excel Data validation require unique number
  • Excel Data validation allow weekday only
  • Excel Data validation don’t exceed total

Get first text value with HLOOKUP in Excel

by

This tutorial shows how to Get first text value with HLOOKUP in Excel using the example below;

Formula

=HLOOKUP("*",range,1,FALSE)

Explanation

To lookup and retrieve the first text value across a range of columns, you can use the HLOOKUP function with a wildcard. In the example shown, the formula in F5 is:

=HLOOKUP("*",C5:E5,1,0)

How this formula works

Sometimes, you may want to check a range of several columns and extract the first text value found. You can do this with the HLOOKUP function and the asterisk wildcard character (*).

Worked Example:   How to use Excel HLOOKUP Function

In the formula shown, HLOOKUP is configured like this:

=HLOOKUP("*",C5:E5,1,0)

The lookup value is “*”, a wildcard that matches one or more text values.

The table array C5:E5, entered as a relative references so that it changes as the formula is copied down column F.

Worked Example:   Excel VLOOKUP and HLOOKUP functions Examples

The row index number is 1, since the range contains only one row.

The range lookup argument is set to zero (false) to force exact match. This is required when using wildcards with VLOOKUP or HLOOKUP.

In each row, HLOOKUP finds and returns the first text value found in columns C through E in each row.

Worked Example:   Excel VLOOKUP and HLOOKUP functions Examples

Ignoring empty strings

To ignore empty strings generated by other formulas, try adjusting wildcards as follows:

=HLOOKUP("?*",range,1,0)

Translated: match text with at least one character.

Note: this formula won’t work for numeric values since the asterisk wildcard matches only text.

Post navigation

Previous Post:

Get first text value in a list in Excel

Next Post:

Get information corresponding to max value in Excel

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
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • How to get last line in cell in Excel
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • Remove line breaks in a cell in Excel
  • How to check if cell contains one of many things in Excel
  • Check If Two Dates are same month in Excel
  • Find Last Day of the Month in Excel
  • Add workdays no weekends in Excel
  • How to calculate percent of year complete in Excel
  • Convert date to text in Excel
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • VDB function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles