VLOOKUP ?

[Keith (Southend)G]

I use the VLOOKUP function a lot but was wondering if it were possible
to use this when there are more than one cell I want to separately use
data from. Currently I have:
=VLOOKUP(B1,Sheet3!$A$1:$B$65,2,FALSE)
but what I can't seem to do is then take the data from the $C$ column,
thus avoiding the $B$ column
=VLOOKUP(B1,Sheet3!$A$1:$C$65,2,FALSE) ~ doesn't work as this includes
column B.

What I have to do at the moment is copy/paste the columns 'C' and 'D'
from the 1st sheet to two new sheets so the data is in column B

Many thanks

Keith (Southend)

 

[Niek Otten]

=VLOOKUP(B1,Sheet3!$A$1:$C$65,3,FALSE)

Note the column number

 

[Pete_UK]

Keith,

the 3rd parameter determines the column number of your table from
which the data is returned. If you set the formulae up like this:

=VLOOKUP(B1,Sheet3!$A$1:$D$65,2,FALSE)
=VLOOKUP(B1,Sheet3!$A$1:$D$65,3,FALSE)
=VLOOKUP(B1,Sheet3!$A$1:$D$65,4,FALSE)

then the first one will bring data from column 2 (i.e. B), the second
one from column 3 (C) and the third one from column 4 (D). Notice that
the lookup table is defined to cover four columns, A to D.

Now, if you want the data to be returned in adjacent columns, eg C, D
and E, then you can use a single formula like this in C1:

=VLOOKUP($B1,Sheet3!$A$1:$D$65,COLUMN(B1),FALSE)

and then just copy this into D1:E1.

Hope this helps.

Pete

 

[Keith (Southend)G]

Keith,

the 3rd parameter determines the column number of your table from
which the data is returned. If you set the formulae up like this:

=VLOOKUP(B1,Sheet3!$A$1:$D$65,2,FALSE)
=VLOOKUP(B1,Sheet3!$A$1:$D$65,3,FALSE)
=VLOOKUP(B1,Sheet3!$A$1:$D$65,4,FALSE)

then the first one will bring data from column 2 (i.e. B), the second
one from column 3 (C) and the third one from column 4 (D). Notice that
the lookup table is defined to cover four columns, A to D.

Now, if you want the data to be returned in adjacent columns, eg C, D
and E, then you can use a single formula like this in C1:

=VLOOKUP($B1,Sheet3!$A$1:$D$65,COLUMN(B1),FALSE)

and then just copy this into D1:E1.

Hope this helps.

Pete

Thanks Pete, I hadn't realized that number determined which column.

Thats simplified things.

Keith (Southend)

 

[Pete_UK]

Glad to help, Keith - thanks for feeding back.

Pete

 

[Shane Devenshire]

Hi,

Considering the previous response you can use a variable for the column
number as follows:

=VLOOKUP($B1,Sheet3!$A$1:$D$65,COLUMN(B1),FALSE)

If you then copy the formula to the right each formula will return a
different column from the data source.

FYI, if you use a range name instead of a cell reference you can eliminate
the sheet reference, for example

=VLOOKUP(B1,Table,2,FALSE)

where the range Sheet3!$A$1:$D$65 has been named Table.

 

[vbaexcel]

I use the VLOOKUP function a lot but was wondering if it were possible
to use this when there are more than one cell I want to separately use
data from. Currently I have:
=VLOOKUP(B1,Sheet3!$A$1:$B$65,2,FALSE)
but what I can't seem to do is then take the data from the $C$ column,
thus avoiding the $B$ column
=VLOOKUP(B1,Sheet3!$A$1:$C$65,2,FALSE) ~ doesn't work as this includes
column B.

What I have to do at the moment is copy/paste the columns 'C' and 'D'
from the 1st sheet to two new sheets so the data is in column B

Many thanks

Keith (Southend)

Hi Keith

Have you tried the more flexible and powerful INDEX/MATCH approach?

Have you discovered SUMPRODUCT?

I guess not, you would not be using VLOOKUP anymore.

See www.excel-examples.com

 

[Niek Otten]

What a nonsense!

For many, if not most people,VLOOKUP is difficult enough.
Please don't disencourage users who discovered VLOOKUP by telling them they
use the wrong tool!
They use the right tool, they just may need a little help exploring all the
options.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

I use the VLOOKUP function a lot but was wondering if it were possible
to use this when there are more than one cell I want to separately use
data from. Currently I have:
=VLOOKUP(B1,Sheet3!$A$1:$B$65,2,FALSE)
but what I can't seem to do is then take the data from the $C$ column,
thus avoiding the $B$ column
=VLOOKUP(B1,Sheet3!$A$1:$C$65,2,FALSE) ~ doesn't work as this includes
column B.

What I have to do at the moment is copy/paste the columns 'C' and 'D'
from the 1st sheet to two new sheets so the data is in column B

Many thanks

Keith (Southend)

Hi Keith

Have you tried the more flexible and powerful INDEX/MATCH approach?

Have you discovered SUMPRODUCT?

I guess not, you would not be using VLOOKUP anymore.

See www.excel-examples.com

 

[Niek Otten]

Although I disagree with your advice, I shouldn't have called it nonsense.

Please accept my apologies.