Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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.

In a recent thread:

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

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:

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)

=MATCH(9.99E+307,B1:B15)

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,

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

Thanks

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For the last

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

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

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

This ignores numbers, if you want the

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

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<>

regards, barry

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialHere'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:

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

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 Amazon.com 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:

• 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

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$1:INDEX($A:$A,MATCH(re

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

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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

for error checking in Excel 2003, use:

=MAX(ROW($B:$B)*IF(ISERROR

for Excel 2007, use:

=MAX(ROW($B:$B)*IFERROR(IS

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