In sheet: 1.1,

Put in C6:
=IF(OR(D6="",$E$3=""),"",
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P1",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S1",match col,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P2",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S2",match col,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P3",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S3",match col,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P4",ma tchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S4",match col,0)),"")))))
C6 copied down

[C6:C18's formulas can be entered simultaneously into all other identically
structured sheets via grouping the sheets, as explained in previous response.
But do remember to ungroup the sheets immediately thereafter]

Here's the implemented sample:
http://cjoint.com/?gsplvpiSJd
Starguy_Sample2.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"starguy" wrote:

I will try to implement both formulas but then I check for excel
calculation speed. I have many other formulas in that workbook as well,
some of which are arrays. Infact arrays slow down speed that's why I
avoid to use them. I have to implement this formula in most of my
sheets and in many rows and the reference data is also large. Array
will definitely slow down speed of calculation therefore I have to have
a substitution of this.
I will appreciate if you help me to complete that formula.

Thank you in anticipation

Regards


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813