Remember Me?

 Tom Posts: n/a Need help with modifying VLookUp

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,FAL SE))

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

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 a
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 Posts: n/a 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

-----Original Message-----
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,FA LSE))

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

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 a
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

.

 Alan Beban Posts: n/a For a different approach for users who have the functions in the freely
workbook, array enter into a 3x2 range

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

Alan Beban

Tom wrote:
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,FAL SE))

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

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 a
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

 Tom Posts: n/a Thanks! That works great.

--
Thanks,
Tom

"Jason Morin" wrote in message
...
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

-----Original Message-----
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,F ALSE))

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

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 a
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

.

 Tom Posts: n/a I'll give it a try. Thanks!

Tom

"Alan Beban" wrote in message
...
For a different approach for users who have the functions in the freely
workbook, array enter into a 3x2 range

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

Alan Beban

Tom wrote:
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,FAL SE))

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

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 a
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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Jeff Excel Discussion (Misc queries) 8 December 1st 04 03:41 PM Smichaud Excel Discussion (Misc queries) 2 November 30th 04 11:51 AM Steve Jones Excel Discussion (Misc queries) 0 November 29th 04 06:01 PM Jeff Excel Discussion (Misc queries) 1 November 26th 04 09:39 PM Jeff Excel Discussion (Misc queries) 2 November 26th 04 06:29 PM

All times are GMT +1. The time now is 12:37 PM. Copyright ©2004-2019 ExcelBanter.