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

Lookup and Reference Examples

  • Lookup entire row in Excel
  • How to get address of named range in Excel
  • How to use Excel COLUMN Function
  • Perform case-sensitive Lookup in Excel
  • How to create dynamic named range with OFFSET in Excel

Data Analysis Examples

  • How To Sort One Column or Multiple Columns in Excel
  • Understanding Anova in Excel
  • Managing Conditional Formatting Rules in Excel
  • Get column index in Excel Table
  • Use Data Form to input, edit and delete records in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation date in specific year
  • Excel Data validation require unique number
  • Excel Data validation allow weekday only
  • Data validation must not exist in list

Get first non-blank value in a list in Excel

by

This tutorial shows how to Get first non-blank value in a list in Excel using the example below;

Formula

{=INDEX(range,MATCH(FALSE,ISBLANK(range),0))}

Explanation

If you need to get the first non-blank value (text or number) in a in a one-column range you can use an array formula based on the INDEX, MATCH, and ISBLANK functions.

In the example the formula we’re using is:

{=INDEX(B3:B11,MATCH(FALSE,ISBLANK(B3:B11),0))}

This is an array formula and must be entered with Control-Shift-Enter.

How this formula works

So, the gist of the problem is this: We want to get the first non-blank cell, but we don’t have a direct way to do that in Excel. We could use VLOOKUP with a wildcard * (see link below), but that will only work for text, not numbers.

Worked Example:   How to generate random number weighted probability in Excel

So, we need to build the functionality we need by nesting formulas. On way to do that is to use an array function that “tests” cells and returns an array of TRUE/FALSE values that we can then match with MATCH.

Worked Example:   Sum range with INDEX in Excel

Working from the inside out, ISBLANK evaluates the cells in the range B3:B11 and returns and array result that looks like this:

{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}

Each FALSE represents a cell in the range that is not blank.

Next, MATCH looks for FALSE inside the array and returns the position of the first match found, in this case 2. At this point, the formula in the example now looks like this:

{=INDEX(B3:B11,2,0))}

Finally, the INDEX function takes over and gets the value at position 2 in the array, in the case the number 10.

Worked Example:   If cell is blank in Excel

First numeric value

To get the first numeric value in a list, you can adapt the formula to use the ISNUMBER function, then change the logic to match TRUE instead of FALSE:

{=INDEX(range,MATCH(TRUE,ISNUMBER(range),0))}

This is also an array formula, and must be entered with control+shift+enter.

Post navigation

Previous Post:

Get employee information with VLOOKUP in Excel

Next Post:

Get first match cell contains 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
  • SUBSTITUTE function vs REPLACE function in Excel
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • Change Case: Uppercase, Lowercase, Propercase in Excel
  • REPT function: Description, Usage, Syntax, Examples and Explanation
  • How to split text string at specific character in Excel
  • Convert date to month and year in Excel
  • Add decimal minutes to time in Excel
  • How to calculate nth day of week in month in Excel
  • How to get workdays between dates in Excel
  • How to get number of days, weeks, months or years between two dates in Excel
  • Tax rate calculation with fixed base in Excel
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate simple interest in Excel
  • How to calculate annuity for interest rate in excel
  • Calculate payment periods for loan in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles