When I enter a VLOOKUP formula in a cell and then copy and paste it all the
way down the remainder of the column, it not only changes the cell of the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in G1:H7
just like it does for C1. I dont want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o G1:H7.

I understand your frustration with the VLOOKUP formula changing the table array when you copy and paste it. Fortunately, there is a simple solution to this problem.

Instead of using absolute cell references (i.e. A1, G1:H7), we can use mixed cell references (i.e. $A1, $G$1:$H$7) to lock the table array in place while allowing the lookup value to change as we copy and paste the formula.

Here's how to do it:

1. Select the cell with the VLOOKUP formula (C1 in your example).
2. Click on the formula bar at the top of the screen to edit the formula.
3. Highlight the table array (G1:H7 in your example).
4. Press the F4 key on your keyboard. This will add dollar signs ($) to the cell references, making them mixed cell references.
5. The table array should now look like $G$1:$H$7. Press Enter to save the formula.
6. Copy the formula from C1 and paste it into C2 and C3.
7. The table array should remain locked in place while the lookup value changes as you copy and paste the formula.

Using mixed cell references is a great way to ensure that your formulas stay consistent as you copy and paste them throughout your worksheet.

Use the $ signs which create an absolute reference. So in cell C1 the formula
would be

=Vlookup(A1,$G$1:$H$7,2,0)

"GKW in GA" wrote:

When I enter a VLOOKUP formula in a cell and then copy and paste it all the
way down the remainder of the column, it not only changes the cell of the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in G1:H7
just like it does for C1. I dont want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o G1:H7.

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it all the
way down the remainder of the column, it not only changes the cell of the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in G1:H7
just like it does for C1. I dont want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o G1:H7.

If the table array is more than 2 columns, is it possible to paste more than
one column into the target cols. Lets say the table-array instead of G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it all the
way down the remainder of the column, it not only changes the cell of the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in G1:H7
just like it does for C1. I dont want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o G1:H7.

All you have to do is include the *entire* datalist cell references in the
formula, and then just change the column index number when you enter the
formula in Column D.

For example, in C1:

=VLOOKUP($A1,$G$1:$I$7,2,0)

And, in D1:

=VLOOKUP($A1,$G$1:$I$7,3,0)

And copy down as needed.

Now, if you have a lot more columns to include in your lookup, you can use
another function within the Vlookup formula to *automatically* increment the
column index number as you copy the original formula across columns, along a
row.
And then copy down as needed.

For example, if you had Columns G to K in your datalist, enter in C1:

=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)

And copy across to F1.

This would *automatically* increment the column index number, from 2 to 5.
Then copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"GKW in GA" wrote in message
...
If the table array is more than 2 columns, is it possible to paste more
than
one column into the target cols. Lets say the table-array instead of
G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in
G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it all
the
way down the remainder of the column, it not only changes the cell of
the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it
to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in
G1:H7
just like it does for C1. I don't want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o
G1:H7.

vlookup(lookup_value,table_array,col_index,[range_lookup])

you can use =VLOOKUP(A1,$G$1:$i$7,3,0) - will return the value on I column
(3rd column)

=VLOOKUP(A1,$G$1:$i$7,2,0) - will return the value on H column (2nd column)

is it answer your question?
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

If the table array is more than 2 columns, is it possible to paste more than
one column into the target cols. Lets say the table-array instead of G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it all the
way down the remainder of the column, it not only changes the cell of the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in G1:H7
just like it does for C1. I dont want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o G1:H7.

yes, I think the *automatic* function is what I am after. I have a lot of
columns sometimes. Btw, shouldn't the $ be omitted from
=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)
do that it becomes
=VLOOKUP(A1,$G$1:$K$7,Rows($1:2),0)

thanks for your help

"RagDyer" wrote:

All you have to do is include the *entire* datalist cell references in the
formula, and then just change the column index number when you enter the
formula in Column D.

For example, in C1:

=VLOOKUP($A1,$G$1:$I$7,2,0)

And, in D1:

=VLOOKUP($A1,$G$1:$I$7,3,0)

And copy down as needed.

Now, if you have a lot more columns to include in your lookup, you can use
another function within the Vlookup formula to *automatically* increment the
column index number as you copy the original formula across columns, along a
row.
And then copy down as needed.

For example, if you had Columns G to K in your datalist, enter in C1:

=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)

And copy across to F1.

This would *automatically* increment the column index number, from 2 to 5.
Then copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"GKW in GA" wrote in message
...
If the table array is more than 2 columns, is it possible to paste more
than
one column into the target cols. Lets say the table-array instead of
G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in
G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it all
the
way down the remainder of the column, it not only changes the cell of
the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it
to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in
G1:H7
just like it does for C1. I don't want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o
G1:H7.

If you copy the formula across 5 columns, that A1 will change to B1, C1, D1,
etc.

That's why I made the column absolute, and left the row relative.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"GKW in GA" wrote in message
...
yes, I think the *automatic* function is what I am after. I have a lot of
columns sometimes. Btw, shouldn't the $ be omitted from
=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)
do that it becomes
=VLOOKUP(A1,$G$1:$K$7,Rows($1:2),0)

thanks for your help

"RagDyer" wrote:

All you have to do is include the *entire* datalist cell references in
the
formula, and then just change the column index number when you enter the
formula in Column D.

For example, in C1:

=VLOOKUP($A1,$G$1:$I$7,2,0)

And, in D1:

=VLOOKUP($A1,$G$1:$I$7,3,0)

And copy down as needed.

Now, if you have a lot more columns to include in your lookup, you can
use
another function within the Vlookup formula to *automatically* increment
the
column index number as you copy the original formula across columns,
along a
row.
And then copy down as needed.

For example, if you had Columns G to K in your datalist, enter in C1:

=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)

And copy across to F1.

This would *automatically* increment the column index number, from 2 to
5.
Then copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"GKW in GA" wrote in message
...
If the table array is more than 2 columns, is it possible to paste more
than
one column into the target cols. Lets say the table-array instead of
G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in
G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it
all
the
way down the remainder of the column, it not only changes the cell
of
the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste
it
to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2
in
G1:H7
just like it does for C1. I don't want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9
i/o
G1:H7.

RagDyer,
I am having trouble understanding the logic of your example
=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0) of how Rows($1:2) causes the column
indexing number to be incremented from 2 to 5. Would you mind explaining.

Here is a practical of something I am actually trying to do:
I have a workbook, BOOK1, that has one column, A. I also have BOOK2 that has
column A thru F. Column A is formatted the same in BOOK1 and BOOK2, in fact
BOOK2.A is a subset of BOOK1.A.

I would like to use VLOOKUP to populate cols B thru F of BOOK2 into BOOK1.xB
to BOOK1.xF in the rows for which BOOK1.xA = BOOK2.xA (x being the row
number)




"RagDyer" wrote:

All you have to do is include the *entire* datalist cell references in the
formula, and then just change the column index number when you enter the
formula in Column D.

For example, in C1:

=VLOOKUP($A1,$G$1:$I$7,2,0)

And, in D1:

=VLOOKUP($A1,$G$1:$I$7,3,0)

And copy down as needed.

Now, if you have a lot more columns to include in your lookup, you can use
another function within the Vlookup formula to *automatically* increment the
column index number as you copy the original formula across columns, along a
row.
And then copy down as needed.

For example, if you had Columns G to K in your datalist, enter in C1:

=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)

And copy across to F1.

This would *automatically* increment the column index number, from 2 to 5.
Then copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"GKW in GA" wrote in message
...
If the table array is more than 2 columns, is it possible to paste more
than
one column into the target cols. Lets say the table-array instead of
G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in
G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it all
the
way down the remainder of the column, it not only changes the cell of
the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste it
to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2 in
G1:H7
just like it does for C1. I don't want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o
G1:H7.

The reason you're having trouble understanding is because I screwed up!<bg
Sorry!

The Rows() function will increment as it's copied down a column.

I should have used the Columns() function, which increments as it's copied
across columns, along a row.

So, this would be a formula:

=VLOOKUP($A1,$G$1:$K$7,Columns($A:B),0)

I apologize for the time you wasted trying to figure out my goof!

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"GKW in GA" wrote in message
...
RagDyer,
I am having trouble understanding the logic of your example
=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0) of how Rows($1:2) causes the column
indexing number to be incremented from 2 to 5. Would you mind explaining.

Here is a practical of something I am actually trying to do:
I have a workbook, BOOK1, that has one column, A. I also have BOOK2 that has
column A thru F. Column A is formatted the same in BOOK1 and BOOK2, in fact
BOOK2.A is a subset of BOOK1.A.

I would like to use VLOOKUP to populate cols B thru F of BOOK2 into BOOK1.xB
to BOOK1.xF in the rows for which BOOK1.xA = BOOK2.xA (x being the row
number)




"RagDyer" wrote:

All you have to do is include the *entire* datalist cell references in the
formula, and then just change the column index number when you enter the
formula in Column D.

For example, in C1:

=VLOOKUP($A1,$G$1:$I$7,2,0)

And, in D1:

=VLOOKUP($A1,$G$1:$I$7,3,0)

And copy down as needed.

Now, if you have a lot more columns to include in your lookup, you can
use
another function within the Vlookup formula to *automatically* increment
the
column index number as you copy the original formula across columns, along
a
row.
And then copy down as needed.

For example, if you had Columns G to K in your datalist, enter in C1:

=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)

And copy across to F1.

This would *automatically* increment the column index number, from 2 to 5.
Then copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"GKW in GA" wrote in message
...
If the table array is more than 2 columns, is it possible to paste more
than
one column into the target cols. Lets say the table-array instead of
G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in
G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it
all
the
way down the remainder of the column, it not only changes the cell of
the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste
it
to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2
in
G1:H7
just like it does for C1. I don't want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o
G1:H7.

It's not a problem. I appreciate your help. I didn't spend much time on it
anyway. When I was using Rows() it was taking a really long time to search,
so I knew something was wrong so I stopped and created this post.

So, explain the Columns($A:B) part of it, if you don't mind, I assume that
$A is the column you looking up in and B is where you start copying from,
right? So, I take it that I would use Columns($A:B) no matter how many
columns were in the table-array, correct?

thanks again

"RagDyeR" wrote:

The reason you're having trouble understanding is because I screwed up!<bg
Sorry!

The Rows() function will increment as it's copied down a column.

I should have used the Columns() function, which increments as it's copied
across columns, along a row.

So, this would be a formula:

=VLOOKUP($A1,$G$1:$K$7,Columns($A:B),0)

I apologize for the time you wasted trying to figure out my goof!

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"GKW in GA" wrote in message
...
RagDyer,
I am having trouble understanding the logic of your example
=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0) of how Rows($1:2) causes the column
indexing number to be incremented from 2 to 5. Would you mind explaining.

Here is a practical of something I am actually trying to do:
I have a workbook, BOOK1, that has one column, A. I also have BOOK2 that has
column A thru F. Column A is formatted the same in BOOK1 and BOOK2, in fact
BOOK2.A is a subset of BOOK1.A.

I would like to use VLOOKUP to populate cols B thru F of BOOK2 into BOOK1.xB
to BOOK1.xF in the rows for which BOOK1.xA = BOOK2.xA (x being the row
number)




"RagDyer" wrote:

All you have to do is include the *entire* datalist cell references in the
formula, and then just change the column index number when you enter the
formula in Column D.

For example, in C1:

=VLOOKUP($A1,$G$1:$I$7,2,0)

And, in D1:

=VLOOKUP($A1,$G$1:$I$7,3,0)

And copy down as needed.

Now, if you have a lot more columns to include in your lookup, you can
use
another function within the Vlookup formula to *automatically* increment
the
column index number as you copy the original formula across columns, along
a
row.
And then copy down as needed.

For example, if you had Columns G to K in your datalist, enter in C1:

=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)

And copy across to F1.

This would *automatically* increment the column index number, from 2 to 5.
Then copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"GKW in GA" wrote in message
...
If the table array is more than 2 columns, is it possible to paste more
than
one column into the target cols. Lets say the table-array instead of
G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in
G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it
all
the
way down the remainder of the column, it not only changes the cell of
the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and paste
it
to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2
in
G1:H7
just like it does for C1. I don't want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9 i/o
G1:H7.

The Columns() function

=Columns($A:B)

has *nothing* to do with where or what your table array is!

The Columns() function (different from the Column() function) *only* returns
a number.

For example,
In A1 enter:
=Columns(A:A)

In A2 enter:
=Columns ($A:A)

In A3 enter:
=Columns(A:$J)

Now, select those 3 cells and drag across to copy to say Column J.

See what happens?

The good part about using this function is that *no matter* where you enter
it, you *always* get the same number.

So,
=Columns($A:B)
returns 2 if entered in A1 or entered in Z100.

You can enter your Lookup formula anywhere, and just adjust the Columns()
function to start with whatever number (column index) you need and it will
increment (or decrement) as you copy it across.

Rows() works exactly the same way, returning numbers as it's copied down.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"GKW in GA" wrote in message
...
It's not a problem. I appreciate your help. I didn't spend much time on it
anyway. When I was using Rows() it was taking a really long time to search,
so I knew something was wrong so I stopped and created this post.

So, explain the Columns($A:B) part of it, if you don't mind, I assume that
$A is the column you looking up in and B is where you start copying from,
right? So, I take it that I would use Columns($A:B) no matter how many
columns were in the table-array, correct?

thanks again

"RagDyeR" wrote:

The reason you're having trouble understanding is because I screwed
up!<bg
Sorry!

The Rows() function will increment as it's copied down a column.

I should have used the Columns() function, which increments as it's copied
across columns, along a row.

So, this would be a formula:

=VLOOKUP($A1,$G$1:$K$7,Columns($A:B),0)

I apologize for the time you wasted trying to figure out my goof!

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"GKW in GA" wrote in message
...
RagDyer,
I am having trouble understanding the logic of your example
=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0) of how Rows($1:2) causes the column
indexing number to be incremented from 2 to 5. Would you mind explaining.

Here is a practical of something I am actually trying to do:
I have a workbook, BOOK1, that has one column, A. I also have BOOK2 that
has
column A thru F. Column A is formatted the same in BOOK1 and BOOK2, in
fact
BOOK2.A is a subset of BOOK1.A.

I would like to use VLOOKUP to populate cols B thru F of BOOK2 into
BOOK1.xB
to BOOK1.xF in the rows for which BOOK1.xA = BOOK2.xA (x being the row
number)




"RagDyer" wrote:

All you have to do is include the *entire* datalist cell references in
the
formula, and then just change the column index number when you enter the
formula in Column D.

For example, in C1:

=VLOOKUP($A1,$G$1:$I$7,2,0)

And, in D1:

=VLOOKUP($A1,$G$1:$I$7,3,0)

And copy down as needed.

Now, if you have a lot more columns to include in your lookup, you can
use
another function within the Vlookup formula to *automatically* increment
the
column index number as you copy the original formula across columns,
along
a
row.
And then copy down as needed.

For example, if you had Columns G to K in your datalist, enter in C1:

=VLOOKUP($A1,$G$1:$K$7,Rows($1:2),0)

And copy across to F1.

This would *automatically* increment the column index number, from 2 to
5.
Then copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"GKW in GA" wrote in message
...
If the table array is more than 2 columns, is it possible to paste
more
than
one column into the target cols. Lets say the table-array instead of
G1:H7,
it's G1:I7. Is there a way to paste Hx:Ix into Cx:Dx if Ax is found in
G1:G7
(where x is the row number)

"Marcelo" wrote:

=VLOOKUP(A1,$G$1:$H$7,2,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"GKW in GA" escreveu:

When I enter a VLOOKUP formula in a cell and then copy and paste it
all
the
way down the remainder of the column, it not only changes the cell
of
the
lookup value, it also changes the row/column of the table - array

Example:
If I enter =VLOOKUP(A1,G1:H7,2,0) in cell C1 and then copy and
paste
it
to
cell C2, it changes C2 to =VLOOKUP(A2,G2:H8,2,0).

What I really want is for C2 to contain the result of looking up A2
in
G1:H7
just like it does for C1. I don't want it to look in G2:H8.

Similarly, pasting the formula into C3 causes it to look in G3:H9
i/o
G1:H7.