Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |