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

Earn up to 10 extra points for answering this tough question.

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.