match data

[Guest]

I need help asap?
I want to match information in four columns
ex
name test1 name test2
tina 86 mark 75
mark 80 tina 85
jim 45 jane 86
jake 51
mary 95
Not every one who took test1 took test2. I want to have the test1 and test2
grades for the students across the same row for those who took both test

 

[Ken Johnson]

name test1 test2 name test2 test1
tina 86 85 mark 75 80
mark 80 75 tina 85 86
jim 45 #N/A jane 86 #N/A
jake 51 #N/A
mary 95 #N/A

I used vlookup in 3rd column

=VLOOKUP(A2,$D$2:$E$6,2,FALSE) filled down three rows

and again in 6th column

=VLOOKUP(D2,$A$2:$B$4,2,FALSE) filled down 5 rows

after inserting these columns for showing both test scores for both
name lists.
Sorry if they haven't lined up very well.

Ken Johnson

 

[Ken Johnson]

Hi Le,
They didn't look that good when I previewed!
Ken Johnson

 

[Ken Johnson]

Hi Le,
I guess it would have been better to have swapped the 5th and 6th
column positions so that test1 is to the left of test 2 for both name
lists.

Then column 3's formula is:
=VLOOKUP(A2,$D$2:$F$6,3,FALSE) filled down 3 rows

and column 5's formula is:
=VLOOKUP(D2,$A$2:$B$4,2,FALSE) filled down 5 rows

I hope you find this useful

Ken Johnson