Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function to return values in multipal Columns
I'm using the VLOOKUP function to look up value in another sheet and return
the corresponding value, My problem is It works for the first column but the how do I get the next 4 columns Sheet 1 has the date that needs to be looked up and once the Employee name is found then return the corresponding values in Column D,E,F Sheet 1 C D E F Employees Volume Hours VPH ABC 100 10 3.3 Result Sheet Formula being used =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to Match the name and get the value in column D in Sheet 1 =VLOOKUP(D5,Sheet1!C$1:D$16,2,FALSE) to get the value of Column E in Sheet 1 for the above match. Hope I explained this correctly. -- Thanks KP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function to return values in multipal Columns
Hi,
Just change the column argument to 3, 4, 5,... The 2 in the following formula is the column argument. =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) You can make it more dynamic by doing something like =VLOOKUP(C5,Sheet1!B$1:C$16,COLUMN(B1),FALSE) then when you copy the formula to the right you will get the results for column 3, then 4, then 5,.... -- If this helps, please click the Yes button. Cheers, Shane Devenshire "KP" wrote: I'm using the VLOOKUP function to look up value in another sheet and return the corresponding value, My problem is It works for the first column but the how do I get the next 4 columns Sheet 1 has the date that needs to be looked up and once the Employee name is found then return the corresponding values in Column D,E,F Sheet 1 C D E F Employees Volume Hours VPH ABC 100 10 3.3 Result Sheet Formula being used =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to Match the name and get the value in column D in Sheet 1 =VLOOKUP(D5,Sheet1!C$1:D$16,2,FALSE) to get the value of Column E in Sheet 1 for the above match. Hope I explained this correctly. -- Thanks KP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function to return values in multipal Columns
Maybe:
=VLOOKUP(C5,Sheet1!B$1:g$16,2,FALSE) =VLOOKUP(C5,Sheet1!B$1:g$16,3,FALSE) =VLOOKUP(C5,Sheet1!B$1:g$16,4,FALSE) =VLOOKUP(C5,Sheet1!B$1:g$16,5,FALSE) =VLOOKUP(C5,Sheet1!B$1:g$16,5,FALSE) KP wrote: I'm using the VLOOKUP function to look up value in another sheet and return the corresponding value, My problem is It works for the first column but the how do I get the next 4 columns Sheet 1 has the date that needs to be looked up and once the Employee name is found then return the corresponding values in Column D,E,F Sheet 1 C D E F Employees Volume Hours VPH ABC 100 10 3.3 Result Sheet Formula being used =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to Match the name and get the value in column D in Sheet 1 =VLOOKUP(D5,Sheet1!C$1:D$16,2,FALSE) to get the value of Column E in Sheet 1 for the above match. Hope I explained this correctly. -- Thanks KP -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function to return values in multipal Columns
Just expand the Table definition part from:
=VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to: =VLOOKUP(C5,Sheet1!B$1:Z$16,2,FALSE) draw from column C =VLOOKUP(C5,Sheet1!B$1:Z$16,3,FALSE) draw from column D =VLOOKUP(C5,Sheet1!B$1:Z$16,4,FALSE) draw from column E =VLOOKUP(C5,Sheet1!B$1:Z$16,5,FALSE) draw from column F =VLOOKUP(C5,Sheet1!B$1:Z$16,6,FALSE) draw from column G etc. -- Gary''s Student - gsnu200838 "KP" wrote: I'm using the VLOOKUP function to look up value in another sheet and return the corresponding value, My problem is It works for the first column but the how do I get the next 4 columns Sheet 1 has the date that needs to be looked up and once the Employee name is found then return the corresponding values in Column D,E,F Sheet 1 C D E F Employees Volume Hours VPH ABC 100 10 3.3 Result Sheet Formula being used =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to Match the name and get the value in column D in Sheet 1 =VLOOKUP(D5,Sheet1!C$1:D$16,2,FALSE) to get the value of Column E in Sheet 1 for the above match. Hope I explained this correctly. -- Thanks KP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function to return values in multipal Columns
Hi
From your description the data to be looked up, starts in column B and ends in column E of Sheet1. I assume that you are always wanting to look up the value from Column C of the Result Sheet sheet. If that is the case, then in your first cell enter =VLOOKUP($C5,Sheet1!$B$1:$E$16,COLUMN(C1),0) Copy across (and down) as required. The third Argument, the offset from the first column in the dataset being looked up, Column(C1) will return 3, as column C is column 3. As you drag across, the column reference will step up to D, E etc, hence the offset will step up to 3, 4 etc. and pick up your required data. Note the Absolute $C5. This fixes column C of results sheet as the item being looked up, otherwise it would alter to D5, E5 as you copy across, and would fail. -- Regards Roger Govier "KP" wrote in message ... I'm using the VLOOKUP function to look up value in another sheet and return the corresponding value, My problem is It works for the first column but the how do I get the next 4 columns Sheet 1 has the date that needs to be looked up and once the Employee name is found then return the corresponding values in Column D,E,F Sheet 1 C D E F Employees Volume Hours VPH ABC 100 10 3.3 Result Sheet Formula being used =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to Match the name and get the value in column D in Sheet 1 =VLOOKUP(D5,Sheet1!C$1:D$16,2,FALSE) to get the value of Column E in Sheet 1 for the above match. Hope I explained this correctly. -- Thanks KP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function to return values in multipal Columns
You don't want to increment the table array or the lookup value like that.
"Lock" the formula onto the first column of the table array and then increment the column index number: =VLOOKUP($C5,Sheet1!$B$1:$L$16,COLUMNS($A1:B1),0) As you copy across it increments the column index number like so: =VLOOKUP($C5,Sheet1!$B$1:$L$16,2,0) =VLOOKUP($C5,Sheet1!$B$1:$L$16,3,0) =VLOOKUP($C5,Sheet1!$B$1:$L$16,4,0) etc etc Adjust for the actual end of the table array: $L$16 -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Just change the column argument to 3, 4, 5,... The 2 in the following formula is the column argument. =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) You can make it more dynamic by doing something like =VLOOKUP(C5,Sheet1!B$1:C$16,COLUMN(B1),FALSE) then when you copy the formula to the right you will get the results for column 3, then 4, then 5,.... -- If this helps, please click the Yes button. Cheers, Shane Devenshire "KP" wrote: I'm using the VLOOKUP function to look up value in another sheet and return the corresponding value, My problem is It works for the first column but the how do I get the next 4 columns Sheet 1 has the date that needs to be looked up and once the Employee name is found then return the corresponding values in Column D,E,F Sheet 1 C D E F Employees Volume Hours VPH ABC 100 10 3.3 Result Sheet Formula being used =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to Match the name and get the value in column D in Sheet 1 =VLOOKUP(D5,Sheet1!C$1:D$16,2,FALSE) to get the value of Column E in Sheet 1 for the above match. Hope I explained this correctly. -- Thanks KP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function to return values in multipal Columns
My apologies.
Your first offset is 2, not 3, hence the starting formula should be =VLOOKUP($C5,Sheet1!$B$1:$E$16,COLUMN(B1),0) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi From your description the data to be looked up, starts in column B and ends in column E of Sheet1. I assume that you are always wanting to look up the value from Column C of the Result Sheet sheet. If that is the case, then in your first cell enter =VLOOKUP($C5,Sheet1!$B$1:$E$16,COLUMN(C1),0) Copy across (and down) as required. The third Argument, the offset from the first column in the dataset being looked up, Column(C1) will return 3, as column C is column 3. As you drag across, the column reference will step up to D, E etc, hence the offset will step up to 3, 4 etc. and pick up your required data. Note the Absolute $C5. This fixes column C of results sheet as the item being looked up, otherwise it would alter to D5, E5 as you copy across, and would fail. -- Regards Roger Govier "KP" wrote in message ... I'm using the VLOOKUP function to look up value in another sheet and return the corresponding value, My problem is It works for the first column but the how do I get the next 4 columns Sheet 1 has the date that needs to be looked up and once the Employee name is found then return the corresponding values in Column D,E,F Sheet 1 C D E F Employees Volume Hours VPH ABC 100 10 3.3 Result Sheet Formula being used =VLOOKUP(C5,Sheet1!B$1:C$16,2,FALSE) to Match the name and get the value in column D in Sheet 1 =VLOOKUP(D5,Sheet1!C$1:D$16,2,FALSE) to get the value of Column E in Sheet 1 for the above match. Hope I explained this correctly. -- Thanks KP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you write a formul that will put multipal answers in multipal. | Excel Worksheet Functions | |||
Can you write a formul that will put multipal answers in multipal. | Excel Worksheet Functions | |||
vlookup 3 columns all return same | Excel Discussion (Misc queries) | |||
How can I use the vlookup function to return a sum of the values? | Excel Discussion (Misc queries) | |||
vlookup function return all values | Excel Worksheet Functions |