Identify the lowest (highest row number) non-numerical entry in a range in Excel

Posted on 2012-04-04
Last Modified: 2012-04-21

Suppose that in an Excel spreadsheet, you have one cell containing a formula ("formula cell"), another "reference cell" which needs to be referenced in the formula cell, and any number of additional cells containing values (value cells).  Furthermore, suppose that the reference cell cannot be defined by either a relative or absolute cell address.

In a recent thread:

a solution was provided for how to specify the reference cell when:
• it's column is known
• it lies within a known range within the column
• it is known to be further down (have a greater row number) than any other value cells in the range
• its value is a number

For the range B1:B15, the solution given in that thread is as follows:

=LOOKUP(9.99E+307,B1:B15)  (to find the value of the reference cell)
=MATCH(9.99E+307,B1:B15)  (to find the relative position of the reference cell)

Now instead of a number, suppose the value in the reference cell is a text entry. Is there a way to determine the same information (i.e. the entry and/or relative position of the reference cell) so that it can be included as part of the formula?

For example, suppose the formula cell is B16 and the range B1:B15 includes a combination of numbers, text entries and blank cells as shown in Fig. 1.In this case, the two formulas return the following results:

=LOOKUP(9.99E+307,B1:B15)  Result =  3
=MATCH(9.99E+307,B1:B15)  Result = 9

indicating that cell B12, containing the word "cat," was ignored (which is  not surprising since the formulas use a number for the lookup/match value).

As mentioned in the previous thread, "it's kind of like the formula is looking or scanning up the column to identify the first or closest cell with a value and then basing the result on that value."

Is there a way to do that when the value is not a number?

Question by:Steve_Brady
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 42

Assisted Solution

dlmille earned 250 total points
ID: 37807619
Yes, you can do that with this formula, just change the $B:$B to the column being examined:


for error checking in Excel 2003, use:


for Excel 2007, use:


These are all array-entered, use CTRL-ALT-SHIFT to confirm.

LVL 50

Accepted Solution

barry houdini earned 250 total points
ID: 37808242
Hello Steve,

For the last number the lookup value used was "bignum", i.e. 9.99E+307, for text you can use "bigtext", a string of 255 "z"s, i.e.


and you can do the same with MATCH, i.e.


This ignores numbers, if you want the last non-blank entry of any type you can use this version


or with MATCH


the last one, only, needs "array entry" or avoid that with this version


regards, barry
LVL 42

Expert Comment

ID: 37808543
Steve, sorry, for some reason I read the question incorrectly - my posted was based on the last numeric.

Here's the same approach for non-numeric:

Yes, you can do that with this formula, just change the $B:$B to the column being examined:


The approach is also array-entered, use CTRL-ALT-SHIFT to confirm.

While its an array formula, I find it easier to remember than other methods, though all are valid and probably the non-array version barry provided is more efficient, somehow - I'm sure he'll tell us.

You can use COLUMN(1:1) ' for row1 - to do the same test to find the max column so there are variations on that as well.


Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

ID: 37817331
Dave & Barry,

Thanks for the great solutions and comments.  FYI, I opted for Barry's (non-array) =LOOKUP() formula:


because using =MATCH() would add additional steps.

Barry, your solutions commonly include these inverse arguments (e.g 1/B1:B15<>"") which leave me totally befuddled — but they always work.  Do you come up with them on your own or are they written down somewhere?  Can I go on and buy a copy of, "Inverse Arguments for Dummies"?  Whenever possible, I much prefer to understand what is going on in a formula rather than just inserting it — but then, the latter still gets the job done.  The other part I still have trouble getting my head around is how a range (versus an individual cell) can be equal to (=) or not equal to (<>) something.  There's no need to address those points here as I already have the solution I asked for in this thread and also because I just started two new threads to ask about each of them:

However, I thought the two of you might be interested in seeing the following application of formulas which your solution enabled me to just complete. It's extremely useful for some things I am working on and might have use in other scenarios as well.

Suppose you have an Excel file containing many rows of text for which chapters (Ch) and verses (Vs) are specified as shown here (Fig. 1):1I wanted a way to insert an additional level of rows for various notes or topics of notes which refer to specific portions of the text as shown in Fig. 2.2I also wanted the ability to insert a second similar level (called sub-notes say) which refer to specific notes in the first level (Fig. 3).3With your help, I was able to create formulas which automatically create the headings for these two levels of notes.  In the first (notes) level, the formula adds increments of .01 to the Ch & Vs heading to which it refers, thus creating the capacity to handle up to 99 separate and uniquely-labeled notes.  The second (sub-notes) level formula adds a single letter (from "a" up to "z") to the first level heading it follows and thereby makes available, an additional 26 sub-notes that can be included for each first level note.  In other words, a single verse can have up to 2574 (99 x 26) inserted pieces of uniquely identified information (comments, references, etc.).

The best part about it to me though is that at either level, the headings self-adjust any time rows are inserted, deleted or moved to a new location (i.e. you do not get incorrect cell references or #REF! results in those cases).

Fig. 4 shows were Barry's formula (using offset functions rather than direct cell references to define the ranges) fits in.4Offset functions are used because unlike absolute and relative cell references, offset functions do not carry any cell reference "memory" when moved to a new location.

The file is attached. To observe its function:

        •copy row 31 and insert it anywhere or
        •copy row 32 and insert it under any note or sub-note or
        •move notes and/or sub-notes to a new location
        •delete any note or sub-note.

In all cases, notice how other notes and sub-notes headings change.

Thanks again for your very valuable input.

LVL 42

Expert Comment

ID: 37817925
Steve - there is a good tutorial Contextures did late 2010 called "30 Excel Functions in 30 days" :

Most of the tricks of the trade can be found there, and they provide great building blocks for developing your own techniques.  Each of us have our "Favorites" - e.g., I almost always use INDEX/MATCH to find the last row or set up a dynamic range, though most tips out there work with Offset.  Since I learned the ANCHOR:INDEX(MATCH) function for a dynamic range as my first lesson in this (actually from matthewspatrick), it stuck with me, and is also not a volatile function (as the most often touted Offset actually is).  I've never found an implementation using Offset that I couldn't do ANCHOR:INDEX/MATCH or INDEX/MATCH:INDEX/MATCH to create a dynamic range with and I can actually remember the formula without having to think.


=OFFSET($A$1,,,1,COUNTA($A:$A)) would be the same as

but Offset is a volatile function, so performance may vary depending on how much you use it.  Its your equivalent of me doing array functions because some of them come more quickly to mind...

That's the same with me as opposed to using the LOOKUP(1, 1/whatever) approach.  I haven't used it enough for it to be natural, and its so easy for me to remember the alternatives even if they are array functions - which are NOT awful formulas at all, and depending on what you're doing, efficiency of either may not even be noticeable.  So, use the functions that you can remember, and if the performance is not there, then opt for other approaches.

Nice library of functions you're building there.  I think you should think seriously about chalking up an article on what you've learned so others can benefit!  How about something in the next 90 days?  




Author Closing Comment

ID: 37876551

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question