look up
 

hi
i have sheet1:
A B
benz 123
jeep 456
fiat 789
benz asd
benz zxc
jeep qwe
fiat opq
.... ...
.... ...
i wate when i tipe: for example benz it return 123
and ets...

asd

zxc

....
regardes
nader

look up
 

Hi,

You may refer to my article at the following site

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nader" wrote in message
...
hi
i have sheet1:
A B
benz 123
jeep 456
fiat 789
benz asd
benz zxc
jeep qwe
fiat opq
... ...
... ...
i wate when i tipe: for example benz it return 123
and ets...

asd

zxc

....
regardes
nader

look up
 

In cell C1 enter the query string

In cell D1 enter the below formula and copy down as required,,Please note
that this is an array formula. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=IF(ROW(A1)<=COUNTIF($A$1:$A$10,$C$1),INDEX($B$1:$ B$10,
SMALL(IF($A$1:$A$10=$C$1,ROW($A$1:$A$10)),ROW(A1)) ),"")

--
Jacob


"nader" wrote:

hi
i have sheet1:
A B
benz 123
jeep 456
fiat 789
benz asd
benz zxc
jeep qwe
fiat opq
... ...
... ...
i wate when i tipe: for example benz it return 123
and ets...

asd

zxc

....
regardes
nader