SOLVED

Formulas and Functions in Excel Sheet

Copper Contributor

I have a Called phone No and name in sheet1, (B2 and G2 field) and sheet2 Called Phone No (A2) have only phone number now I want how to get name in E-6 field  from Sheet1 in excel sheet ?

 

Sheet1

Phone NoCalled Phone NoCall DateCall TimeType of CallDurationNameAddress
91980090666691760233449701-12-202210:45:58CALL-IN151Dipankar DasAsansol, West Bengal713325
91980090666691963505258001-12-202211:48:02SMS-IN0Amrit KoleyPurulia, West Bengal, 713325

 

 

Sheet2

Called Phone NoName ?
917602334497 
919635052580 
4 Replies
You can use the VLOOKUP function in Excel to retrieve the name from Sheet1 and display it in the E6 field of Sheet2.
The formula would be =VLOOKUP(A2,Sheet1!$B$2:$H$3,7,FALSE).
This formula searches for the value in A2 of Sheet2 in the second column of Sheet1 (Called Phone No) and returns the value from the seventh column of Sheet1 (Name) in the same row where the match is found.
best response confirmed by partha537 (Copper Contributor)
Solution
#REFF
=VLOOKUP(A2,Sheet1!$B$2:$G$2,7,FALSE) Respected Sir, I am tried to execute the Vlook up formula in excel in connection with Call details record (CDR analysis), now my question is clear that in sheet2 I have multiple numbers in Called Phone Number (A2 to A3521) and Multiple Subscriber Name in Colum no. 7, (G2 to G3521) and Sheet 3 I have a pivot table with multiple number (A2 to A251) to count of calls (incoming, Outgoing, SMS in, SMS Out). Now I want Subscriber name in Pivot table (G6). Sheet2 CDRPhoneNo CalledPhoneNo CallDate CallTime TypeofCall Duration Sub Name_B Sub Address_B Tower Name _A TowerAddress_A Colum 7 is Subscriber Name Sheet3 (Pivot Table) Count of Duration Column Labels Row Labels CALL-IN CALL-OUT SMS-IN SMS-OUT Grand Total Subscriber Name ? 916294825540 1769 1644 100 14 3527 913413567420 1 1 Subscriber Name ?

@partha537 

Is Sheet1 the real name of the first sheet that you mentioned? If not, use the real name in the formula.

Respected Sir,
I am tried to execute the Vlook up formula in excel in connection with Call details record (CDR analysis), now my question is clear that in sheet2 I have multiple numbers in Called Phone Number (A2 to A3521) and Multiple Subscriber Name in Colum no. 7, (G2 to G3521) and Sheet 3 I have a pivot table with multiple number (A2 to A251) to count of calls (incoming, Outgoing, SMS in, SMS Out). Now I want Subscriber name in Pivot table (G6).

Sheet2

CDRPhoneNo CalledPhoneNo CallDate CallTime TypeofCall Duration Sub Name_B Sub Address_B Tower Name _A TowerAddress_A

Colum 7 is Subscriber Name

Sheet3 (Pivot Table)

Count of Duration Column Labels
Row Labels CALL-IN CALL-OUT SMS-IN SMS-OUT Grand Total Subscriber Name ?
916294825540 1769 1644 100 14 3527
913413567420 1 1 Subscriber Name ?

1 best response

Accepted Solutions
best response confirmed by partha537 (Copper Contributor)
Solution
#REFF
=VLOOKUP(A2,Sheet1!$B$2:$G$2,7,FALSE) Respected Sir, I am tried to execute the Vlook up formula in excel in connection with Call details record (CDR analysis), now my question is clear that in sheet2 I have multiple numbers in Called Phone Number (A2 to A3521) and Multiple Subscriber Name in Colum no. 7, (G2 to G3521) and Sheet 3 I have a pivot table with multiple number (A2 to A251) to count of calls (incoming, Outgoing, SMS in, SMS Out). Now I want Subscriber name in Pivot table (G6). Sheet2 CDRPhoneNo CalledPhoneNo CallDate CallTime TypeofCall Duration Sub Name_B Sub Address_B Tower Name _A TowerAddress_A Colum 7 is Subscriber Name Sheet3 (Pivot Table) Count of Duration Column Labels Row Labels CALL-IN CALL-OUT SMS-IN SMS-OUT Grand Total Subscriber Name ? 916294825540 1769 1644 100 14 3527 913413567420 1 1 Subscriber Name ?

View solution in original post