"pappu" wrote:
Looks like it will solve my problem. Thanks a lot. But my data is in
almost 15000 rows. Will it be applicable?

It should still work, notwithstanding ...

As the calculation overhead is going v.heavy, try switching the book's calc
mode to Manual first via clicking: Tools Options Calculation tab Check
Manual OK
Then when all formulas are filled, just press F9 to recalc (Go for a short
break, it might take awhile for recalc to complete <g)

Then just adapt the ranges within the topline formulas for cols E to N to
suit the reference source data extent (15000?). For example:

In E2, array-entered*:
=INDEX('1'!$C$2:$C$100,MATCH(1,(ISNUMBER(SEARCH(TR IM(D2),TRIM('1'!$D$2:$D$100))))*('1'!$D$2:$D$100< ""),0))

would become:
=INDEX('1'!$C$2:$C$15000,MATCH(1,(ISNUMBER(SEARCH( TRIM(D2),TRIM('1'!$D$2:$D$15000))))*('1'!$D$2:$D$1 5000<""),0))

Then fill down the topline formulas in C2:N2 as far as you have data in
sheet: 2
(I don't know how many lines you have in sheet: 2)

Sorry, I don't understand about Excel Array.

The special way to confirm/register the array formulas in cols E to N and in
col C via pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) is
crucial. If correctly confirmed, Excel will then insert/wrap curly braces { }
around the formula. You can see/verify this in the formula bar.

Note: *Do not* type these curly braces into the formulas
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---