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?