# Microsoft Excel - excel problem - Asked By anu anu on 26-Jun-12 07:47 AM

hi ,

i have some data in excel worbook , in 2 worksheet one master sheet and other sheet one .in master sheet i have data like

 Serial Call ID c opn date c opn time 0173584 DL656050 1-Dec-11 10:27 0173584 DL655976 2-Dec-11 14:03

and in sheet one data like

 Serial Date Time 0173584 1-Dec-11 10:27

i want data in sheet one Like below:

 Serial Date Time Call id 0173584 1-Dec-11 10:27 DL656050

CAll id take from master sheet against Serial no but according to date and time.

Pichart Y. replied to anu anu on 26-Jun-12 11:44 AM
Hi Usha anu,

I just post the answer for you in another post...

You can do with 2 alternatives, normal formula or array formula..
I show you both ways here.

• Normal formula, seems to be easy but you must create 1 more column to create the Data index. From my sample attachment, it is column E
• input this formula in E2 =A2&C2&D2 then drag the formula down.
• in cell K2 input this formula =INDEX(B2:B7,MATCH(H2&I2&J2,E2:E7,0),1)
• Now about the Array function.
• no need to use the Data index field. We will create it by the Array function...here it is
• in cell K2 input =INDEX(\$B\$2:B7,MATCH(H2&I2&J2,\$A\$2:A7&\$C\$2:C7&\$D\$2:D7,0),1)
• when finish instead of enter, hold ctrl+shift and hit enter
• You will find the { } round your formula..this called Array function.
• like this {=INDEX(\$B\$2:B7,MATCH(H2&I2&J2,\$A\$2:A7&\$C\$2:C7&\$D\$2:D7,0),1)}
• Sample file for you --->ArrFml_IndexArray.zip
Hope this help

Pichart Y.
wally eye replied to anu anu on 26-Jun-12 02:21 PM
Pichart just beat me to it.  Here is the formula with your sheet names:

=INDEX(Master!B\$2:B\$3,MATCH(A2&B2&C2,Master!A\$2:A\$3&Master!C\$2:C\$3&Master!D\$2:D\$3,0))

Just adjust the ranges to match your worksheet.  Master column B here contains the Call ID, with the remaining info in A, C and D, Sheet1 has Serial, Date and Time in A, B and C.
anu anu replied to Pichart Y. on 27-Jun-12 06:25 AM
hi ,

thanks but whenever i drag the formula the same value copy and when i double click on cell then it`s show the calculation

Pichart Y. replied to anu anu on 27-Jun-12 12:37 PM
Hi make sure, you set your calculation mode auto calculation

pichart Y.