Excel Lookup

B

Brian

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,
 
B

Brian

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
 
B

Biff

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
 
Top