MATCH
in [General]
|
Prev: Splitting Data from One Worksheet to Many Automatically
Next: Sorting Cells with Conditional Formats
From: Sasikiran on 27 Apr 2010 09:14 Hello, I am looking for a formula which looks for 2 specific text values which are there in sheet1, matches with the same text in sheet2 and displays the corresponding value which is there in sheet2 on a cell in sheet1. Let me explain with an example. Sheet1 has data similar to this Date Time Emp Number Name Designation = = 23 Bob Manager The cell which displays Time should match the Name and Designation in sheet1 with the same Name and Designation in sheet2 and display the corresponding Date and Time value present in sheet2 on to sheet1 Sheet2 has raw data in these headers Date Time Emp Number Name 27-Apr 18:00 23 Bob 27-Apr 18:22 26 Kate Please help
From: Eduardo on 27 Apr 2010 09:23 Hi, your information is in column A to E in both sheets, so in sheet 1 to bring the date enter =index(sheet2!$A$2:$A$10000,match(D2,sheet2!$D$2:$D$10000,0),match(E2,sheet2!$E$2:$E$10000,0)) in cell B2 enter =index(sheet2!$B$2:$B$10000,match(D2,sheet2!$D$2:$D$10000,0),match(E2,sheet2!$E$2:$E$10000,0)) "Sasikiran" wrote: > Hello, > > I am looking for a formula which looks for 2 specific text values which are > there in sheet1, matches with the same text in sheet2 and displays the > corresponding value which is there in sheet2 on a cell in sheet1. > > Let me explain with an example. > > Sheet1 has data similar to this > > Date Time Emp Number Name Designation > = = 23 Bob Manager > > > The cell which displays Time should match the Name and Designation in sheet1 > with the same Name and Designation in sheet2 and display the corresponding > Date and Time value present in sheet2 on to sheet1 > > Sheet2 has raw data in these headers > > Date Time Emp Number Name > 27-Apr 18:00 23 Bob > 27-Apr 18:22 26 Kate > > > Please help >
From: Jacob Skaria on 27 Apr 2010 09:26
Hope you have only one entry in Sheet2 for an employee name/designation In cell A2 =SUMPRODUCT((Sheet2!D1:D100=D2)*(Sheet2!E1:E100=E2),Sheet2!A1:A100) In cell B2 =SUMPRODUCT((Sheet2!D1:D100=D2)*(Sheet2!E1:E100=E2),Sheet2!B1:B100) Employee number is unique...so the below SUMIF() should do in your case.... =SUMIF(Sheet2!C:C,C3,Sheet1!A:A) PS: Please note that the above formulas are only specific to your data... -- Jacob (MVP - Excel) "Sasikiran" wrote: > Hello, > > I am looking for a formula which looks for 2 specific text values which are > there in sheet1, matches with the same text in sheet2 and displays the > corresponding value which is there in sheet2 on a cell in sheet1. > > Let me explain with an example. > > Sheet1 has data similar to this > > Date Time Emp Number Name Designation > = = 23 Bob Manager > > > The cell which displays Time should match the Name and Designation in sheet1 > with the same Name and Designation in sheet2 and display the corresponding > Date and Time value present in sheet2 on to sheet1 > > Sheet2 has raw data in these headers > > Date Time Emp Number Name > 27-Apr 18:00 23 Bob > 27-Apr 18:22 26 Kate > > > Please help > |