MATCH
 

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

MATCH
 

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

MATCH
 

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