Need help with modifying VLookUp

[Tom]

I need some help with modifying a VLOOKUP function.

I have the following data in cell range E1:G9.

Col_E Col_F Col_G
===== ===== =====
Richard 1 A
Richard 2 B
Richard 3 C
Sue 4 D
Sue 5 E
Sue 6 F
Tom 7 G
Tom 8 H
Tom 9 I


Cell A1 will have either one of the 3 names: Richard, Sue, Tom


Then, in cell range A15:B17, I want to show the associated values of either
name:
- F1:G3, or
- F4:G6, or
- F7:G9

So, if A1 = Sue, the I want to show the following in A15:B17:
4 D
5 E
6 F


Right now, I have the following VLOOKUP in A15, A16, and A17.
=IF($A$1="","",VLOOKUP($A$1,Sheet2!$E$1:$G$9,2,FALSE))

and in B15, B16, B17
=IF($D$1="","",VLOOKUP($D$1,Sheet2!$E$1:$G$9,3,FALSE))


Okay, here's my problem... when e.g. "Sue" (A1), then it's taking the 1st
occurance of "Sue" and cross-references it for all in range A15:B17. So,
right now, the results are:
4 D
4 D
4 D

Again, how do I change the VLookup so I get (for "Sue")...
4 D
5 E
6 F


Thanks,
Tom

 

[Jason Morin]

Put this in A15, press ctrl/shift/enter, and copy over
and down:

=INDEX(F$1:F$9,SMALL(IF($A$1=$E$1:$E$9,ROW($E$1:$E$9)),ROW
()-14))

HTH
Jason
Atlanta, GA

 

[Alan Beban]

For a different approach for users who have the functions in the freely
downloadable file at http://home.pacbell.net/beban available to their
workbook, array enter into a 3x2 range

=VLookups(A1,E1:G9,{2,3})

Alan Beban

 

[Tom]

Thanks! That works great.

--
Thanks,
Tom

Put this in A15, press ctrl/shift/enter, and copy over
and down:

=INDEX(F$1:F$9,SMALL(IF($A$1=$E$1:$E$9,ROW($E$1:$E$9)),ROW
()-14))

HTH
Jason
Atlanta, GA

 

[Tom]

I'll give it a try. Thanks!

Tom

For a different approach for users who have the functions in the freely
downloadable file at http://home.pacbell.net/beban available to their
workbook, array enter into a 3x2 range

=VLookups(A1,E1:G9,{2,3})

Alan Beban