I want to look down a column until I find a value eg. 10/May/05
I want to look across that row until I find a value eg. "CWS" and then return
the value of the cell one to the right of the cell containing "CWS".
I tried to use a use a lookup formula containing a match formula to find the
column
but the match formula requires a specified range. In my cenario my range
varies
depending upon the date selected. This range needs to be specific to one
row but change depending upon the row selected.
Here is the formula I came up with but doesn't work because the range in the
match section is rigid.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
The range to look in always covers the same columns but only covers one row
and that row is specific to the date looked up.
any Ideas

Larry,

=INDEX(OFFSET(INDEX(Database!A:A,MATCH(F7,Database !A:A,FALSE)),0,0,1,256),MA
TCH("CWS",OFFSET(INDEX(Database!A:A,MATCH(F7,Datab ase!A:A,FALSE)),0,0,1,256)
)+1)

All on one line.

HTH,
Bernie
MS Excel MVP


"Larry" wrote in message
...
I want to look down a column until I find a value eg. 10/May/05
I want to look across that row until I find a value eg. "CWS" and then
return
the value of the cell one to the right of the cell containing "CWS".
I tried to use a use a lookup formula containing a match formula to find
the
column
but the match formula requires a specified range. In my cenario my range
varies
depending upon the date selected. This range needs to be specific to one
row but change depending upon the row selected.
Here is the formula I came up with but doesn't work because the range in
the
match section is rigid.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
The range to look in always covers the same columns but only covers one
row
and that row is specific to the date looked up.
any Ideas

Larry,

Assuming that the date is in A20, and the string in B20, then try

=INDEX(A1:AQ20,MATCH(A21,A1:A20,0),MATCH(B21,INDIR ECT("A"&MATCH(A21,A1:A20,0
)&":AQ"&MATCH(A21,A1:A20,0)),0)+1)

--
HTH

Bob Phillips

"Larry" wrote in message
...
I want to look down a column until I find a value eg. 10/May/05
I want to look across that row until I find a value eg. "CWS" and then
return
the value of the cell one to the right of the cell containing "CWS".
I tried to use a use a lookup formula containing a match formula to find
the
column
but the match formula requires a specified range. In my cenario my range
varies
depending upon the date selected. This range needs to be specific to one
row but change depending upon the row selected.
Here is the formula I came up with but doesn't work because the range in
the
match section is rigid.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
The range to look in always covers the same columns but only covers one
row
and that row is specific to the date looked up.
any Ideas

Hi Bernie,

By using OFFSET, you don't need the +1 do you?

=INDEX(OFFSET(INDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1 ,256),MATCH("CWS",OFFSET(I
NDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1,256)))

--
HTH

Bob Phillips

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Larry,


=INDEX(OFFSET(INDEX(Database!A:A,MATCH(F7,Database !A:A,FALSE)),0,0,1,256),MA

TCH("CWS",OFFSET(INDEX(Database!A:A,MATCH(F7,Datab ase!A:A,FALSE)),0,0,1,256)
)+1)

All on one line.

HTH,
Bernie
MS Excel MVP


"Larry" wrote in message
...
I want to look down a column until I find a value eg. 10/May/05
I want to look across that row until I find a value eg. "CWS" and then
return
the value of the cell one to the right of the cell containing "CWS".
I tried to use a use a lookup formula containing a match formula to find
the
column
but the match formula requires a specified range. In my cenario my
range
varies
depending upon the date selected. This range needs to be specific to
one
row but change depending upon the row selected.
Here is the formula I came up with but doesn't work because the range in
the
match section is rigid.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
The range to look in always covers the same columns but only covers one
row
and that row is specific to the date looked up.
any Ideas

Bob,

Actually, your version simply returns the "CWS" value, not the value to the
right, which is why I used the +1. To remove the +1, you need to index the
first offset:

=INDEX(OFFSET(INDEX(A:A,MATCH(F7,A:A,FALSE)),0,1,1 ,255),MATCH("CWS",OFFSET(I
NDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1,256)))

Note the change to 0,1,1,255 from 0,0,1,256.

HTH,
Bernie
MS Excel MVP


"Bob Phillips" wrote in message
...
Hi Bernie,

By using OFFSET, you don't need the +1 do you?


=INDEX(OFFSET(INDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1 ,256),MATCH("CWS",OFFSET(I
NDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1,256)))

--
HTH

Bob Phillips

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Larry,



=INDEX(OFFSET(INDEX(Database!A:A,MATCH(F7,Database !A:A,FALSE)),0,0,1,256),MA


TCH("CWS",OFFSET(INDEX(Database!A:A,MATCH(F7,Datab ase!A:A,FALSE)),0,0,1,256)
)+1)

All on one line.

HTH,
Bernie
MS Excel MVP


"Larry" wrote in message
...
I want to look down a column until I find a value eg. 10/May/05
I want to look across that row until I find a value eg. "CWS" and then
return
the value of the cell one to the right of the cell containing "CWS".
I tried to use a use a lookup formula containing a match formula to
find
the
column
but the match formula requires a specified range. In my cenario my
range
varies
depending upon the date selected. This range needs to be specific to
one
row but change depending upon the row selected.
Here is the formula I came up with but doesn't work because the range
in
the
match section is rigid.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
The range to look in always covers the same columns but only covers
one
row
and that row is specific to the date looked up.
any Ideas

Not in my test in didn't Bernie, I had abc next to it, and that was what it
returned, until I removed the +1. Odd!

Bob

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Actually, your version simply returns the "CWS" value, not the value to
the
right, which is why I used the +1. To remove the +1, you need to index
the
first offset:


=INDEX(OFFSET(INDEX(A:A,MATCH(F7,A:A,FALSE)),0,1,1 ,255),MATCH("CWS",OFFSET(I
NDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1,256)))

Note the change to 0,1,1,255 from 0,0,1,256.

HTH,
Bernie
MS Excel MVP


"Bob Phillips" wrote in message
...
Hi Bernie,

By using OFFSET, you don't need the +1 do you?



=INDEX(OFFSET(INDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1 ,256),MATCH("CWS",OFFSET(I
NDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1,256)))

--
HTH

Bob Phillips

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Larry,




=INDEX(OFFSET(INDEX(Database!A:A,MATCH(F7,Database !A:A,FALSE)),0,0,1,256),MA



TCH("CWS",OFFSET(INDEX(Database!A:A,MATCH(F7,Datab ase!A:A,FALSE)),0,0,1,256)
)+1)

All on one line.

HTH,
Bernie
MS Excel MVP


"Larry" wrote in message
...
I want to look down a column until I find a value eg. 10/May/05
I want to look across that row until I find a value eg. "CWS" and
then
return
the value of the cell one to the right of the cell containing "CWS".
I tried to use a use a lookup formula containing a match formula to
find
the
column
but the match formula requires a specified range. In my cenario my
range
varies
depending upon the date selected. This range needs to be specific
to
one
row but change depending upon the row selected.
Here is the formula I came up with but doesn't work because the
range
in
the
match section is rigid.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
The range to look in always covers the same columns but only covers
one
row
and that row is specific to the date looked up.
any Ideas

Bob,

I don't know how it could. For example, if you have the date in column A,
"CWS" in column B, and "abc" in column C, your formula reduces to

=INDEX({38475,"CWS","abc"},MATCH("CWS",{38475,"CWS ","abc"}))

=INDEX({38475,"CWS","test"},2)

or

"CWS"

HTH,
Bernie
MS Excel MVP


"Bob Phillips" wrote in message
...
Not in my test in didn't Bernie, I had abc next to it, and that was what
it
returned, until I removed the +1. Odd!

Bob

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Actually, your version simply returns the "CWS" value, not the value to
the
right, which is why I used the +1. To remove the +1, you need to index
the
first offset:



=INDEX(OFFSET(INDEX(A:A,MATCH(F7,A:A,FALSE)),0,1,1 ,255),MATCH("CWS",OFFSET(I
NDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1,256)))

Note the change to 0,1,1,255 from 0,0,1,256.

HTH,
Bernie
MS Excel MVP


"Bob Phillips" wrote in message
...
Hi Bernie,

By using OFFSET, you don't need the +1 do you?




=INDEX(OFFSET(INDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1 ,256),MATCH("CWS",OFFSET(I
NDEX(A:A,MATCH(F7,A:A,FALSE)),0,0,1,256)))

--
HTH

Bob Phillips

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Larry,





=INDEX(OFFSET(INDEX(Database!A:A,MATCH(F7,Database !A:A,FALSE)),0,0,1,256),MA




TCH("CWS",OFFSET(INDEX(Database!A:A,MATCH(F7,Datab ase!A:A,FALSE)),0,0,1,256)
)+1)

All on one line.

HTH,
Bernie
MS Excel MVP


"Larry" wrote in message
...
I want to look down a column until I find a value eg. 10/May/05
I want to look across that row until I find a value eg. "CWS" and
then
return
the value of the cell one to the right of the cell containing
"CWS".
I tried to use a use a lookup formula containing a match formula
to
find
the
column
but the match formula requires a specified range. In my cenario
my
range
varies
depending upon the date selected. This range needs to be specific
to
one
row but change depending upon the row selected.
Here is the formula I came up with but doesn't work because the
range
in
the
match section is rigid.
=(VLOOKUP(F7,Database!$A:$AQ,
(MATCH("CWS",Database!A6:AQ6,0))+1,FALSE))
The range to look in always covers the same columns but only
covers
one
row
and that row is specific to the date looked up.
any Ideas