MYSTERY: NESTED INDEX AND MATCH FUNCTIONS

[T. Valko]

Good luck!

--
Biff
Microsoft Excel MVP

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.

 

[David Biddulph]

The double unary minus -- is being used to coerce a text string to a number.
If A1 contains a text string 42 (either by being entered as '42, or as
="42", or as the result of a formula returning a text string), =-A1 would
return -42, but =--A1 would return 42.

 

[JingleRock]

Glenn,

I will have to send my sample data from my home computer (when I click
on the link here, I get a "Websense" pop-up; it is our Network
Security System at my office).

I will be sending you a workbook including the two Sheets we have been
talking about plus the VBA Code (I disabled the 'ThisWorkbook' module
which contains code that automatically updates the Data Sheet).

Also, you will see that the Data Sheet is a slightly more complicated
situation:
- instead of just CURRENT PRICE, I also want to extract PREVIOUS
PRICE; and
- for CURRENT PRICE, there is an intervening string of ' 7.52 '
that needs to be ignored; the UDFs in the 'modUtils'
module handle this situation as well as the simpler situation
for PREVIOUS PRICE.

I am looking forward to your comments.

JingleRock

 

[JingleRock]

The double unary minus -- is being used to coerce a text string to a number.
If A1 contains a text string 42 (either by being entered as '42, or as
="42", or as the result of a formula returning a text string), =-A1 would
return -42, but =--A1 would return 42.

Thanks David,
The formula makes much more sense to me now.
JingleRock

 

[JingleRock]

Glenn,

This is the link to my sample data: http://www.savefile.com/files/2038013
Also, refer to my comments about this file that I posted this
afternoon.

JingleRock

 

[Glenn]

Glenn,

This is the link to my sample data: http://www.savefile.com/files/2038013
Also, refer to my comments about this file that I posted this
afternoon.

JingleRock

Put this array formula (commit with CTRL+SHIFT+ENTER) in Values!B4:

=MAX(IF(ISERROR(SEARCH(A4,Data!$A$1:$A$1000)),"",
--RIGHT(TRIM(Data!$A$1:$A$1000),LEN(TRIM(Data!$A$1:$A$1000))-
SEARCH("/|\",SUBSTITUTE(TRIM(Data!$A$1:$A$1000)," ","/|\",
LEN(TRIM(Data!$A$1:$A$1000))-LEN(SUBSTITUTE(TRIM(Data!$A$1:$A$1000),
" ","")))))))

This will search Data!A1:A1000 for the value in cell Value!A4 ("Current Price")
and return the last "word" in that cell, converted to a number.

Copy this formula down to Values!B5 and you should get your "Previous Price".

For what it's worth, a COMPLETE description of your data makes this a lot
easier. The "slightly more complicated" data, specifically the leading text
before "Current Price" and the intervening rounded value before the target value
in the "Current Price" row, forced a slightly different path to the answer.

If there are more variances in your data, this solution could still fail...

 

[JingleRock]

Glenn,

You are truly an EXCEL Function wizard!
As you know, both of the extracts work just fine.
I have learned a lot from you; thanks very much.
One last lesson: what's the story on '/|\'?

Thanks again,

JingleRock

 

[Glenn]

Glenn,

You are truly an EXCEL Function wizard!
As you know, both of the extracts work just fine.
I have learned a lot from you; thanks very much.
One last lesson: what's the story on '/|\'?

Thanks again,

JingleRock

Just a string of characters not expected to found in your data. Could have been
almost anything.

 

[Glenn]

Glenn,

You are truly an EXCEL Function wizard!
As you know, both of the extracts work just fine.
I have learned a lot from you; thanks very much.
One last lesson: what's the story on '/|\'?

Thanks again,

JingleRock

Glad I could help. Funny how neither INDEX or MATCH ended up in the final
solution...

 

[T. Valko]

Try this:

=--TRIM(RIGHT(SUBSTITUTE(TRIM(LOOKUP(
2,1/SEARCH(A4,Data!A4:A52),Data!A4:A52))
," ",REPT(" ",255)),255))