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

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

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

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

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

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

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