Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 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 |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with VBA | Excel Discussion (Misc queries) | |||
Vlookup returns incorrect match | Excel Discussion (Misc queries) | |||
Vlookup / Objects help XL2003 | Excel Discussion (Misc queries) | |||
Vlookup w/ VB | Excel Discussion (Misc queries) | |||
Problem with vlookup | Excel Discussion (Misc queries) |