One non-array formulas play which seems to be able to deliver what you want
(if I've read your intent correctly)
Assume the data posted is in Sheet1,
cols A to F, from row1 down
We'll use 3 empty cols to the right , say cols H, I & J
Put:
in H1: = --ISNUMBER(MATCH(Sheet2!$A$1,B1:F1,0))
in I1: = --ISNUMBER(MATCH(Sheet2!$B$1,B1:F1,0))
in J1: =IF(SUM(H1:I1)=2,ROW(),"")
Select H1:J1, fill down to say, J100,
to cover the max expected data range
In Sheet2
----------
A1:B1 will be where you enter inputs for values 1 & 2
(Enter the 2 values: 0.105, 0.156 into A1:B1)
Put in A2:
=IF(ISERROR(SMALL(Sheet1!$J:$J,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$J:$J,ROWS($A$1:A1)),Sheet1!$J:$J,0)))
Copy A2 across to F2, fill down to F101
(cover the same range size as done in the cols H - J in Sheet1)
Format A2:A101 as dates
Sheet2 will return the desired results from Sheet1 for the inputs made in
A1:B1, all neatly bunched at the top, with blank rows below
For the sample data posted, you'd get:
0.105 0.156 << Inputs in A1:B1
16-Aug-05 0.156 0.132 0.123 0.105 0.114
21-Aug-05 0.105 0.113 0.118 0.156 0.118
28-Aug-05 0.105 0.156 0.109 0.107 0.139
< blank rows
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Linda" wrote in message
...
Aha! I have not been idle. There is a programing site here. I am not a
programer, but I think I am finding answers, but don't know how to
implement
them.
A B C D E F
8/13/05 0.156 0.163 0.108 0.124 0.103
8/14/05 0.134 0.173 0.077 0.109 0.105
8/15/05 0.142 0.155 0.105 0.105 0.103
8/16/05 0.156 0.132 0.123 0.105 0.114
8/17/05 0.118 0.122 0.077 0.115 0.118
8/18/05 1.088 0.127 0.112 0.119 0.116
8/19/05 0.116 0.119 0.155 0.124 0.105
8/20/05 0.094 0.105 0.132 0.147 0.127
8/21/05 0.105 0.113 0.118 0.156 0.118
8/22/05 0.133 0.118 0.115 0.147 0.116
8/23/05 0.156 0.121 0.116 0.139 0.118
8/24/05 0.133 0.131 0.105 0.129 0.119
8/25/05 0.116 0.128 0.099 0.111 0.105
8/26/05 0.127 0.133 0.118 0.105 0.119
8/27/05 0.118 0.148 0.108 0.099 0.124
8/28/05 0.105 0.156 0.109 0.107 0.139
These are the readings for the last 16 days. There are actually 27 items,
but these are the ones I can control and they are in these same colums in
Excel .
I need to look for 0.156 as value 1 and 0.105 as value 2. Value 1 could
be
highlighted Yellow and bold, Value 2 could be highlighted pale green and
bold.
I think a Loop to go through columns B thru F would do it. I also saw
that
you could have the VBA program tell you the location of the values that
match
up at 5, 10, and 15 row intervals. How do I do this?
By reading other posts, I realized clarity in my post was an issue. I
appologize!
Thank you for your time and patience.
--
Linda
"Linda" wrote:
I select a value (1st number), then I need to find a second value
located 5,
10 and 15 readings previous. The information is 90 rows long and 5
columns
wide. Because these values don't always show up at 5, 10 and 15 readings
apart, I have to keep looking until they do. The readings don't need to
show
up together in a group. I need one result for 5, then another for 10
and
another for 15.
I've been using conditional formatting to highlight the two numbers in
the
range of B2:F91 and counting out manually when the readings show up
togetherin the 5, 10 and 15 spans. Readings are being taken once a day,
so
the range changes every day.
I've been looking at Match, IF, Offset and MMult, I think Match is out
because I can't figure out how to make it look for the last set first
and
match two values at the same time. I'm thinking it's going to be a
combination in an array formula.
I'm working on macros and am picking up a little VBA, but I'm still
mostly
lost.
Any assistance will be Greatly appreciated, Thank you.
--
Linda