I need to be able to look in an array that contains both text and numbers for
a text a value in a row. When that value is found I need the amount from the
100 row in the column where the text value I was searching for was found. I
have tried using hlookup, lookup, find but have not had any success. Can
anyone be of assistance.

Thanks,

Need a more detailed explanation.

Biff

"Brian" wrote in message
...
I need to be able to look in an array that contains both text and numbers
for
a text a value in a row. When that value is found I need the amount from
the
100 row in the column where the text value I was searching for was found.
I
have tried using hlookup, lookup, find but have not had any success. Can
anyone be of assistance.

Thanks,

Biff,

Here a quick expample
A B C
D

1 Name Rt 1 Rt 2
Rt 3

3 Net Sales 100.00 200.00
300.00

5 Name Rt 4 Rt 5
Rt 6

7 Net Sales 400.00 500.00
600.00

I would want the array to search A1:D7 find the cell with the text Rt 5 then
supply the amount that is two rows below it. in this case 500.00.

Does this example help?

Thanks,

Brian
"Biff" wrote:

Need a more detailed explanation.

Biff

"Brian" wrote in message
...
I need to be able to look in an array that contains both text and numbers
for
a text a value in a row. When that value is found I need the amount from
the
100 row in the column where the text value I was searching for was found.
I
have tried using hlookup, lookup, find but have not had any success. Can
anyone be of assistance.

Thanks,

Ok.......

Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER:

H1 = lookup value = RT 5

=OFFSET(A1,MAX((A1:D7=H1)*(ROW(A1:D7)))+1,MAX((A1: D7=H1)*(COLUMN(A1:D7)))-1)

Biff

"Brian" wrote in message
...
Biff,

Here a quick expample
A B C
D

1 Name Rt 1 Rt 2
Rt 3

3 Net Sales 100.00 200.00
300.00

5 Name Rt 4 Rt 5
Rt 6

7 Net Sales 400.00 500.00
600.00

I would want the array to search A1:D7 find the cell with the text Rt 5
then
supply the amount that is two rows below it. in this case 500.00.

Does this example help?

Thanks,

Brian
"Biff" wrote:

Need a more detailed explanation.

Biff

"Brian" wrote in message
...
I need to be able to look in an array that contains both text and
numbers
for
a text a value in a row. When that value is found I need the amount
from
the
100 row in the column where the text value I was searching for was
found.
I
have tried using hlookup, lookup, find but have not had any success.
Can
anyone be of assistance.

Thanks,