[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

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

Posted on 2012-04-04
Medium Priority
452 Views
Hello,

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.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27608986.html

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?

Thanks
0
[X]
###### 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
• 3
• 2

LVL 42

Assisted Solution

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

=MAX(ROW(\$B:\$B)*ISNUMBER(\$B:\$B))

for error checking in Excel 2003, use:

=MAX(ROW(\$B:\$B)*IF(ISERROR(ISNUMBER(\$B:\$B)),0,ISNUMBER(\$B:\$B)))

for Excel 2007, use:

=MAX(ROW(\$B:\$B)*IFERROR(ISNUMBER(\$B:\$B),0))

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

Dave
0

LVL 50

Accepted Solution

barry houdini earned 1000 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.

=LOOKUP(REPT("z",255),B1:B15)

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

=MATCH(REPT("z",255),B1:B15)

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

=LOOKUP(2,1/(B1:B15<>""),B1:B15)

or with MATCH

=MATCH(2,1/(B1:B15<>""))

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

=MATCH(2,INDEX(1/(B1:B15<>""),0))

regards, barry
0

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:

=MAX(ROW(\$B:\$B)*ISTEXT(\$B:\$B))

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.

Cheers,

Dave
0

Author Comment

ID: 37817331
Dave & Barry,

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

=LOOKUP(2,1/(B1:B15<>""),B1:B15)

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27665631.html
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27665633.html

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):I 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.I 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).With 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.Offset 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.

EE-2012.04-06.xlsm
0

LVL 42

Expert Comment

ID: 37817925
Steve - there is a good tutorial Contextures did late 2010 called "30 Excel Functions in 30 days" : http://blog.contextures.com/archives/2011/01/02/30-excel-functions-in-30-days-01-exact/

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.

e.g.,

=OFFSET(\$A\$1,,,1,COUNTA(\$A:\$A)) would be the same as
=\$A\$1:INDEX(\$A:\$A,MATCH(rept("z",20),\$A:\$A)

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?

;)

Cheers,

Dave
0

Author Closing Comment

ID: 37876551
Thanks
0

## Featured Post

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month14 days, 2 hours left to enroll