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
  • 3
  • 2
LVL 41

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 41

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.


ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!


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 41

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

777 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