VLOOKUP Function to return values in multipal Columns

[KP]

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$16,2,FALSE) to get the value of Column E in Sheet 1
for the above match.

Hope I explained this correctly.

 

[Shane Devenshire]

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,....

 

[Dave Peterson]

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)

 

[Gary''s Student]

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.

 

[Roger Govier]

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.

 

[T. Valko]

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

 

[Roger Govier]

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

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.