|
Use Match or Vlookup?
I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2)
from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
Use Match or Vlookup?
Hi Erin
Try the following formula in Sheet1! E2 copied down/across: =INDEX(Sheet2!E$2:E$4,MATCH(1,(Sheet2!$A$2:$A$4=Sh eet1!$A2)*(Sheet2!$B $2:$B$4=Sheet1!$B2)*(Sheet2!$D$2:$D$4=Sheet1!$D2), 0)) this is an array formula which must be entered with Ctrl+Shift+Enter (ie not with simply Enter - excel will surround with curly braces {} if it has worked). Hope this helps! Richard On Feb 25, 8:49 pm, ECLynn <ECL99 wrote: I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
Use Match or Vlookup?
Try this:
Entered in Sheet 1 cell D2: =SUMPRODUCT(--(Sheet2!$A$2:$A$4=$A2),--(Sheet2!$B$2:$B$4=$B2),--(ISNUMBER(SEARCH(MID($C2,FIND(" ",$C2)+1,255),Sheet2!$D2:$D4))),Sheet2!E2:E4) Copy across then down Biff "ECLynn" <ECL99 wrote in message ... I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
Use Match or Vlookup?
Erin,
Your post appears in Ariel (a proportional font that screws up the horizontal spacing), even though I've set my read font in OE to Courier New (non-proportional). Maybe someone will tell me why. Why?, I wonder. VLOOKUP will do it. try this in D2, FOR Acc % 1 (untested): =VLOOKUP(C2, Sheet2!$D$2:F4,2, FALSE) And for Acc % 2, in E2: =VLOOKUP(C2, Sheet2!$D$2:F4, 3 ,FALSE) You'll have to change the range referenced in Sheet2 to reflect the true size of your table. Copy down with fill handle. -- Earl Kiosterud www.smokeylake.com On the other hand, you have different fingers. ----------------------------------------------------------------------- "ECLynn" <ECL99 wrote in message ... I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Y ear Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
Use Match or Vlookup?
=SUMPRODUCT(--(Sheet2!$A$2:$A$300=2006),--(Sheet2!$B$2:$B$300=7),--(Sheet2!$D$2:$D$300="mama"),Sheet2!$E$2:$E$300)
Adjust to suit "ECLynn" wrote: I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
Use Match or Vlookup?
I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and
Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Here's one way. On Sheet 2 put these formulas and copy down: G2: =A2&" "&B2&" " & D2 H2: =E2 I2: =F2 On Sheet 1 put these formulas and copy down: E2 =VLOOKUP(A2&" "&B2&" " & D2,Sheet2!G:I,2,FALSE) F2: =VLOOKUP(A2&" "&B2&" " & D2,Sheet2!G:I,3,FALSE) Hide the extra columns on Sheet 2 if necessary. Modify to suit. |
Use Match or Vlookup?
I kind of figured the space would cause a line break and &*!# things up!
Here it is again split into bite sized chunks: =SUMPRODUCT(--(Sheet2!$A$2:$A$4=$A2), --(Sheet2!$B$2:$B$4=$B2), --(ISNUMBER(SEARCH(MID($C2,FIND(" ",$C2)+1,255), Sheet2!$D2:$D4))),Sheet2!E2:E4) Biff "T. Valko" wrote in message ... Try this: Entered in Sheet 1 cell D2: =SUMPRODUCT(--(Sheet2!$A$2:$A$4=$A2),--(Sheet2!$B$2:$B$4=$B2),--(ISNUMBER(SEARCH(MID($C2,FIND(" ",$C2)+1,255),Sheet2!$D2:$D4))),Sheet2!E2:E4) Copy across then down Biff "ECLynn" <ECL99 wrote in message ... I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
Use Match or Vlookup?
Am I the only one that sees that these are different:
SMG Smith, George STR Rayan, Steve HVJ Johnson, Howard Smith, George Rayan, Steve Johnson, Howard If the first group is consistent with a 3 character string in front of the name: =SUMPRODUCT(--(Sheet2!$A$2:$A$4=$A2),--(Sheet2!$B$2:$B$4=$B2),--(Sheet2!$D$2:$D$4=MID($C2,5,255)),Sheet2!E$2:E$4) Biff "T. Valko" wrote in message ... I kind of figured the space would cause a line break and &*!# things up! Here it is again split into bite sized chunks: =SUMPRODUCT(--(Sheet2!$A$2:$A$4=$A2), --(Sheet2!$B$2:$B$4=$B2), --(ISNUMBER(SEARCH(MID($C2,FIND(" ",$C2)+1,255), Sheet2!$D2:$D4))),Sheet2!E2:E4) Biff "T. Valko" wrote in message ... Try this: Entered in Sheet 1 cell D2: =SUMPRODUCT(--(Sheet2!$A$2:$A$4=$A2),--(Sheet2!$B$2:$B$4=$B2),--(ISNUMBER(SEARCH(MID($C2,FIND(" ",$C2)+1,255),Sheet2!$D2:$D4))),Sheet2!E2:E4) Copy across then down Biff "ECLynn" <ECL99 wrote in message ... I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
Use Match or Vlookup?
Erin,
Oops. I didn't notice you said it needs to reference by month and year. Does that mean that there are multiple entries for each person in sheet 2, with different months and years? Your data doesn't show that. Should it just pull data by month and year, and ignore the person's name? -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "ECLynn" <ECL99 wrote in message ... I have 2 seperate sheets. I want to pull the accuracy rates (Acc%1 and Acc%2) from sheet #2 to sheet #1- it needs to reference by month and year. How can I do this? Help! Sheet 1 A B C D E F Year Month TSR Name Acc % 1 Acc%2% 2 2006 7 SMG Smith, George 3 2006 7 STR Rayan, Steve 4 2006 7 HVJ Johnson, Howard Sheet 2 A B C D E F Year Month1 EE# TSR Acc % 1 Acc % 2 1 2006 7 32958 Smith, George 17.9% 74.3% 2 2006 7 32558 Rayan, Steve 5.9% 98.1% 3 2006 7 13538 Johnson, Howard 88.0% 90.5% Thanks! -- Erin |
| All times are GMT +1. The time now is 10:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com