Use Match or Vlookup?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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=Sheet1!$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
 
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
 
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.
 
=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
 
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%
Click to expand...

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.
 
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
 
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
 
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?
 
Back
Top