Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
I have a sheet with 5 colums,
A B C D 1 B C D E 2 C D E F 3 What I want is, using the vlookup function to right! So the numbers are the lookup value =VLOOKUP(B13,B3:G8,-2,FALSE) If I use this, I get an error. Who can help me??? Greets |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
=INDEX(B3:B8,MATCH(B13,D3:D8,0))
HTH -- AP a écrit dans le message de news: ... I have a sheet with 5 colums, A B C D 1 B C D E 2 C D E F 3 What I want is, using the vlookup function to right! So the numbers are the lookup value =VLOOKUP(B13,B3:G8,-2,FALSE) If I use this, I get an error. Who can help me??? Greets |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
Hi
1. VLOOKUP counts columns relatively to range passed to it. I.e. when you pass the range B3:G8, then the first column is B, the second is C, etc. Any column references outside passed range are meaningless for VLOOKUP. NB! 3rd parameter in VLOOKUP really indicates column number in passed range, not it's relative position to some other column. 2. The 1st column in range passed to VLOOKUP is ALWAYS the one, where the condition value is looked for. In your situation, either redesign your sheet, or use INDEX and MATCH instead, something like =INDEX($A$3:$A$8,MATCH($B$13,$B$3:$B$8,0),0) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) wrote in message ups.com... I have a sheet with 5 colums, A B C D 1 B C D E 2 C D E F 3 What I want is, using the vlookup function to right! So the numbers are the lookup value =VLOOKUP(B13,B3:G8,-2,FALSE) If I use this, I get an error. Who can help me??? Greets |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
Thank you both!! Greets |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
Thank you both!! Greets |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
Thank you both!! Greets |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
AP,
I tried the formula you suggested in this post, but it's not working for me. I have the following values: C D E "Rptg Dist" Dist District Name 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 6218 ROMEOVILLE 6218 6218 ROMEOVILLE 6218 6218 ROMEOVILLE I need to look up the value under "reporting dist" which will always be the same value for the whole list, in the "dist" column (which will have several "unsorted" values), when it finds a match, I want it to return the name of that district which will be under "Dist Name" column. The column headings start on row7 and row 8 is left blank for using the autofilter command, data starts on row 9 and the length of the list varies. So the answer for this ex should be "Romeoville" in cell H3 I have to post this today and this is the only thing keeping me from finishing this sheet. Please help! Thanks! -- Hile "Ardus Petus" wrote: =INDEX(B3:B8,MATCH(B13,D3:D8,0)) HTH -- AP a écrit dans le message de news: ... I have a sheet with 5 colums, A B C D 1 B C D E 2 C D E F 3 What I want is, using the vlookup function to right! So the numbers are the lookup value =VLOOKUP(B13,B3:G8,-2,FALSE) If I use this, I get an error. Who can help me??? Greets |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
On Mon, 3 Jul 2006 11:36:01 -0700, hile trotman
wrote: AP, I tried the formula you suggested in this post, but it's not working for me. I have the following values: C D E "Rptg Dist" Dist District Name 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 6218 ROMEOVILLE 6218 6218 ROMEOVILLE 6218 6218 ROMEOVILLE I need to look up the value under "reporting dist" which will always be the same value for the whole list, in the "dist" column (which will have several "unsorted" values), when it finds a match, I want it to return the name of that district which will be under "Dist Name" column. The column headings start on row7 and row 8 is left blank for using the autofilter command, data starts on row 9 and the length of the list varies. So the answer for this ex should be "Romeoville" in cell H3 I have to post this today and this is the only thing keeping me from finishing this sheet. Please help! Thanks! =VLOOKUP($A$9,$B$9:$C$100,2,FALSE) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
Thanks Richard but this won't work either, I can't specify an array with cell
numbers because the number of rows will change from report to report and it can be in the thousands and i don't know where the reporting dist # will be in column d, the array will have to be $D:$E. But it doesn't work. It's picking up the first name in column E. I had false as a parameter which means the list does not need to be sorted but i don't think it wants to work. I've seen other ways in this forum by using INDEX and MATCH, but I don't understand the posts or how these 2 functions work together so I haven't been able to use that either. Any other help you can provide will be appreciated. Thanks! -- Hile "Richard Buttrey" wrote: On Mon, 3 Jul 2006 11:36:01 -0700, hile trotman wrote: AP, I tried the formula you suggested in this post, but it's not working for me. I have the following values: C D E "Rptg Dist" Dist District Name 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 224 WEST CHICAGO 6218 6218 ROMEOVILLE 6218 6218 ROMEOVILLE 6218 6218 ROMEOVILLE I need to look up the value under "reporting dist" which will always be the same value for the whole list, in the "dist" column (which will have several "unsorted" values), when it finds a match, I want it to return the name of that district which will be under "Dist Name" column. The column headings start on row7 and row 8 is left blank for using the autofilter command, data starts on row 9 and the length of the list varies. So the answer for this ex should be "Romeoville" in cell H3 I have to post this today and this is the only thing keeping me from finishing this sheet. Please help! Thanks! =VLOOKUP($A$9,$B$9:$C$100,2,FALSE) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup function to right!
On Wed, 5 Jul 2006 09:20:02 -0700, hile trotman
wrote: Thanks Richard but this won't work either, I can't specify an array with cell numbers because the number of rows will change from report to report and it can be in the thousands and i don't know where the reporting dist # will be in column d, the array will have to be $D:$E. But it doesn't work. It's picking up the first name in column E. I had false as a parameter which means the list does not need to be sorted but i don't think it wants to work. I've seen other ways in this forum by using INDEX and MATCH, but I don't understand the posts or how these 2 functions work together so I haven't been able to use that either. Any other help you can provide will be appreciated. Thanks! Hi, I only specified up to row 100 as an example. If you can't be certain of the number of rows, just change the last reference to 65536, i.e. =VLOOKUP($A$9,$D$9:$E$65536,2,FALSE) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pastable function using VLOOKUP? | Excel Worksheet Functions | |||
Vlookup worksheet function | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |