MYSTERY: NESTED INDEX AND MATCH FUNCTIONS

J

JingleRock

My formula is in a sheet named "Values"; the only other sheet is named
"Data". The workbook contains EXCEL VBA code; Automatic Calculation
is selected.

The format of the Functions I am using is: INDEX (array, row_num) and
MATCH (lookup_value, lookup_array, match_type).

In the Data Sheet, Col A and Col B are "column"-formatted as
"General". Col A has a blank row, a label row, and then 50 rows of
alphanumeric strings (one of which contains the string "CURRENT PRICE"
followed by five spaces and then a 6-digit number, this alphanumeric
sub-string could be in a number of different rows; only one per day).
Col B has a blank row, a label row, and then in Cell B3, there is an
User Defined Function (UDF): '= GetCurrentPrice (A3, "CURRENT
PRICE")'; this formula is copied in each cell down to Cell B52,
inclusive. When displaying Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.

My formula is: ' = INDEX (Data! B:B, MATCH ("", Data! B:B, 1) + 1) '
The concept is for MATCH to find the row_num of the cell that is
immediately above the row containing the "CURRENT PRICE" sub-string;
then add 1 to it and then INDEX has the correct row_num. INDEX does
the rest, pulling in the CURRENT PRICE to the Values Sheet.

Sometimes my formula works and sometimes I get a value of zero.
Because the number of alphanumeric strings in the Data Sheet Col A is
a variable, I have been experimenting with the following: instead of
using 'B:B' in my formula, I have been using 'B3:B100' as an INDEX
argument and 'B3:B90' as a MATCH argument. Also, I have copied, in
the Data Sheet, my UDF in each cell (Col B) down to Cell B100,
inclusive.

A few other thoughts: I believe copying my UDF to the maximum
expected number of data rows is critical. During my experiments, I
was testing the INDEX Function by itself and the MATCH Function by
itself. It seems that, sometimes, MATCH would come up with 'a
relative position' row reference to the bottom row in the Data Sheet
(it was, apparently, ignoring the CURRENT PRICE value). << Why is
this?

OK, now my formula is working with the following argument -- 'B1:B100'
-- for both INDEX and MATCH. This surprises me: it seems that Cell
B1 being empty and Cell B2 containing a Column Label would "screw-up"
the MATCH Function. << ???

Can anyone shed light on this situation?

Thanks,
JingleRock
 
T

T. Valko

Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.
Click to expand...

If "current price" is a numeric value try this:

=LOOKUP(1E100,Data!B:B)
 
J

JingleRock

Col B, Cells B3 through B52 are blank,
If "current price" is a numeric value try this:

=LOOKUP(1E100,Data!B:B)
Click to expand...

Biff,

Thanks for your response.

I think that I didn't explain my situation very well, so I will
elaborate.
'CURRENT PRICE' consists of 6 digits and a decimal embedded in a
string; this price will change daily. This sub-string will be in an
indeterminable row on a daily basis and will be of the form ...
" CURRENT PRICE 6.54321 ". My UDF will
extract the '6.54321' and place the result in Col B (formatted as
"General"). There will only be one such string in the Data Sheet
daily; that is why there will be only one displayed value in the range
B3:B52 (the UDF evaluates to a blank cell if there is no "CURRENT
PRICE" in that row). The trick is getting the day's CURRENT PRICE to
the Values Sheet.

JingleRock
 
T

T. Valko

Did you try the formula?

=LOOKUP(1E100,Data!B:B)

That formula will return the *last numeric value* from Data column B.

You say your UDF extracts the numeric portion from a string and places that
*one numeric value* somewhere in Data column B.

If there is only one numeric value in Data column B then it also has to be
the *last numeric value* and the above formula will find it.

If the formula didn't work (will return #N/A if no numeric values are found)
then you don't have any numeric values in Data column B. Could be your UDF
returns a TEXT number.

Try this generic version. It'll work on both text and numbers.

=LOOKUP(2,1/(Data!B3:B52<>""),Data!B3:B52)
 
T

T. Valko

Come to think of it, this is much easier than we're making it out to be.

If there is only 1 *numeric value* in the range this will suffice:

=MAX(Data!B3:B52)
 
G

Glenn

JingleRock said:
Biff,

Thanks for your response.

I think that I didn't explain my situation very well, so I will
elaborate.
'CURRENT PRICE' consists of 6 digits and a decimal embedded in a
string; this price will change daily. This sub-string will be in an
indeterminable row on a daily basis and will be of the form ...
" CURRENT PRICE 6.54321 ". My UDF will
extract the '6.54321' and place the result in Col B (formatted as
"General"). There will only be one such string in the Data Sheet
daily; that is why there will be only one displayed value in the range
B3:B52 (the UDF evaluates to a blank cell if there is no "CURRENT
PRICE" in that row). The trick is getting the day's CURRENT PRICE to
the Values Sheet.

JingleRock
Click to expand...

If the description of your data above is accurate, try this array formula
(commit with CTRL+SHIFT+ENTER):

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE",""))))
 
J

JingleRock

Biff and Glenn,

Thank you both for your solutions.
Biff, you are right, my UDF returns a TEXT number for CURRENT PRICE.
So, ' =LOOKUP(2,1/(Data!B3:B52<>""),Data!B3:B52) ' works fine.
And the same for ' =MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!
B1:B1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(Data!B1:B1000,"CURRENT PRICE",""))))) ' (I did have
to add a closing paren at the end.)

Do each of you have any comments as to which is preferable?

A few comments:
o "CURRENT PRICE", and the corresponding TEXT number, will
always (99.9999999% reliability) exist.
o Each of the two solutions works with the range B1:B1000 (even
though my UDF was not copied below row 52);
also, remember that B1 is totally empty ("") and B2 contains
an alphabetic string. However, neither solution
would work with the range B:B (too bad).

Each of the solutions is way over my EXCEL knowledge. If each of you
could give me a few words as to what is happening in your solution, I
would appreciate it.

Thanks again,
JingleRock
 
G

Glenn

JingleRock said:
Biff and Glenn,

Thank you both for your solutions.
Biff, you are right, my UDF returns a TEXT number for CURRENT PRICE.
So, ' =LOOKUP(2,1/(Data!B3:B52<>""),Data!B3:B52) ' works fine.
And the same for ' =MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!
B1:B1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(Data!B1:B1000,"CURRENT PRICE",""))))) ' (I did have
to add a closing paren at the end.)

Do each of you have any comments as to which is preferable?

A few comments:
o "CURRENT PRICE", and the corresponding TEXT number, will
always (99.9999999% reliability) exist.
o Each of the two solutions works with the range B1:B1000 (even
though my UDF was not copied below row 52);
also, remember that B1 is totally empty ("") and B2 contains
an alphabetic string. However, neither solution
would work with the range B:B (too bad).

Each of the solutions is way over my EXCEL knowledge. If each of you
could give me a few words as to what is happening in your solution, I
would appreciate it.

Thanks again,
JingleRock
Click to expand...

The array formula solution I provided was meant to replace your UDF and column B
entirely. The references to column A are intentional. You could put this
formula directly in your Values sheet. Something like this:

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!A1:A1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(Data!A1:A1000,"CURRENT PRICE",""))))
 
T

T. Valko

Do each of you have any comments as to which is preferable?

Well, of course I think my suggestion is preferable! <g>

I don't know exactly what your UDF is doing other than extracting part of a
string, but, based on which string and under what conditions, so I can't
comment on Glenn's approach.

You said the string "Current Price nnnnnn" may appear many times in your
range so I don't know which instance of that string you're interested in.
 
G

Glenn

T. Valko said:
Well, of course I think my suggestion is preferable! <g>

I don't know exactly what your UDF is doing other than extracting part of a
string, but, based on which string and under what conditions, so I can't
comment on Glenn's approach.

You said the string "Current Price nnnnnn" may appear many times in your
range so I don't know which instance of that string you're interested in.
Click to expand...


Actually, the OP stated...

"There will only be one such string in the Data Sheet daily"
 
T

T. Valko

Glenn said:
Actually, the OP stated...

"There will only be one such string in the Data Sheet daily"
Click to expand...

Yeah, that's the one instance their UDF has extracted. Where is this
instance of the string in the "raw data" and which one does the UDF extract?
 
J

JingleRock

Actually, the OP stated...
Actually, the OP stated...

"There will only be one such string in the Data Sheet daily"
Click to expand...

Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work. I am posting a snippet of some of my EXCEL VBA code below.

In a regular module:
'Determine the number of records read
numRows = ws.QueryTables("BondData").ResultRange.Columns("A").Rows.
_
Count + 2

'Clear the scrubbing category formulas
ws.Range("B3", "B65536").ClearContents

ws.Cells(3, "B").value = "=GetCurrentPx($A3,""CURRENT PRICE"")"
ws.Range("B4", "B" & numRows) = ws.Cells(3, "B").FormulaR1C1

In another regular module:
Option Explicit

Public Function GetCurrentPx(ByVal s As String, ByVal v As String) As
String
GetCurrentPx = FindString(s, v)
End Function

Private Function FindString(ByVal s As String, ByVal v As String) As
String
If InStr(s, v) > 0 Then
FindString = Trim(Replace(Right(s, Len(s) - InStr(s, v) + 1), v,
vbNullString))
Else
FindString = vbNullString
End If
End Function

Any comments?
JingleRock
 
J

JingleRock

Actually, the OP stated...
"There will only be one such string in the Data Sheet daily"
Click to expand...

Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work. I am posting a snippet of some of my EXCEL VBA code
below.

In a regular module:
'Determine the number of records read
numRows = ws.QueryTables("BondData").ResultRange.Columns("A").Rows.
_
Count + 2

'Clear the scrubbing category formulas
ws.Range("B3", "B65536").ClearContents

ws.Cells(3, "B").value = "=GetCurrentPx($A3,""CURRENT PRICE"")"
ws.Range("B4", "B" & numRows) = ws.Cells(3, "B").FormulaR1C1

In another regular module:
Option Explicit
Public Function GetCurrentPx(ByVal s As String, ByVal v As String) As
String
GetCurrentPx = FindString(s, v)
End Function

Private Function FindString(ByVal s As String, ByVal v As String) As
String
If InStr(s, v) > 0 Then
FindString = Trim(Replace(Right(s, Len(s) - InStr(s, v) + 1), v,
vbNullString))
Else
FindString = vbNullString
End If
End Function

Any comments?
JingleRock
 
T

T. Valko

Ok, if there's only 1 instance of "CURRENT PRICE" in column A, where is it
in relation to the other data in column A? It sounds like it may be the
*last* entry in the column. Can you give us several representative samples
of what the "CURRENT PRICE" string looks like.

This is probably a lot easier than we're making it out to be.
 
G

Glenn

JingleRock said:
Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work.
Click to expand...


Did you try entering it with the references to column A on sheet Data and did it
give you the correct answer? If not, then I would need to see a sample of your
data, not of your code, because I don't do much programming (and I don't think
it is necessary to get the results you desire).
 
G

Glenn

Glenn said:
Did you try entering it with the references to column A on sheet Data
and did it give you the correct answer? If not, then I would need to
see a sample of your data, not of your code, because I don't do much
programming (and I don't think it is necessary to get the results you
desire).
Click to expand...


A modification in case there are any cells in column A that have only numbers in
them:

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE","")))+
ISERROR(FIND("CURRENT PRICE",A1:A1000)),"",
--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE",""))))

Again, this is an array formula that should be committed with CTRL+SHIFT+ENTER.
 
J

JingleRock

A modification in case there are any cells in column A that have only numbers in
them:

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE","")))+
ISERROR(FIND("CURRENT PRICE",A1:A1000)),"",
--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE",""))))

Again, this is an array formula that should be committed with CTRL+SHIFT+ENTER.- Hide quoted text -
Click to expand...

Glenn, the above formula evaluates to zero (I added the reference to
Data!); it also evaluates to zero when referring to Col B in Data
Sheet. However, the preceding version you sent does extract the
correct CURRENT PRICE when referring to Col B. I think I will stick
with that. Thanks very much for your help. And Biff, a big thank you
to you as well.

JingleRock

P.S.: What does the --TRIM Function do? I know about the TRIM
Function.
 
J

JingleRock

P.S.:  What does the --TRIM Function do?  I know about the TRIM Function.

The following works as well.

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!B1:B1000,"",""))),"",--TRIM
(SUBSTITUTE(Data!B1:B1000,"",""))))

JingleRock
 
J

JingleRock

The following works as well.
=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!B1:B1000,"",""))),"",--TRIM
(SUBSTITUTE(Data!B1:B1000,"",""))))

JingleRock
Click to expand...

The following works as well.
=MAX(IF(ISERROR(--TRIM(Data!B1:B1000)),"",--TRIM(Data!B1:B1000)))

JingleRock
 
G

Glenn

JingleRock said:
Glenn, the above formula evaluates to zero (I added the reference to
Data!); it also evaluates to zero when referring to Col B in Data
Sheet. However, the preceding version you sent does extract the
correct CURRENT PRICE when referring to Col B. I think I will stick
with that. Thanks very much for your help. And Biff, a big thank you
to you as well.

JingleRock

P.S.: What does the --TRIM Function do? I know about the TRIM
Function.
Click to expand...

There is no point in using this formula if you are still using your UDF and
column B. If you would like me to take a look at your workbook (or sample
data), save it to www.savefile.com and post the link here. I will take a look
and see if I can figure out why it's not working as expected.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Index and Match 3
index/match error 2
Index and Match 8
Index Match to add employee number 2
Index Match on Columns and Rows 0
Index, Match help 3
Using Index and Match Formula 2
INDEX - MATCH - OFFSET By Reference 2

Top