Hi! All

I have data in Sheet 1like

Its a code

5848606
9659285
8100949
4846771
7201303

And in Sheet2 i have master Sheet. Data Range A1:Z600

Now i want to Pickup the Data From Sheet 2 i.e B2 to Z2 to Sheet1
I use VLOOKUP(A1,Sheet2!a2:b600,2,false)
It Work Fine for me .

Is it possible to use only one Formula

Or i have to type one by one Vlookup Formula to pickup data from column in
sheet2 Like

VLOOKUP(A1,Sheet2!a2:b600,2,false)
VLOOKUP(A1,Sheet2!a2:c600,3,false)
VLOOKUP(A1,Sheet2!a2:d600,4,false)
VLOOKUP(A1,Sheet2!a2:e600,5,false)
And so on

Thanks in Advance

Hardeep kanwar

This is one of the reasons that I prefer using Index / Match. It allows me to
drag the formula...

=Index(Sheet2!B$2:B$600, match($A$1, Sheet2!$A$2:$A$600, 0))
Dragging to the right you get...
=Index(Sheet2!C$2:C$600, match($A$1, Sheet2!$A$2:$A$600, 0))

You could do this using the row() or column() function but that makes the
formula volatile which attaches a lot of unnecessary overhead to the
calculation.
--
HTH...

Jim Thomlinson


"Hardeep kanwar" wrote:

Hi! All

I have data in Sheet 1like

Its a code

5848606
9659285
8100949
4846771
7201303

And in Sheet2 i have master Sheet. Data Range A1:Z600

Now i want to Pickup the Data From Sheet 2 i.e B2 to Z2 to Sheet1
I use VLOOKUP(A1,Sheet2!a2:b600,2,false)
It Work Fine for me .

Is it possible to use only one Formula

Or i have to type one by one Vlookup Formula to pickup data from column in
sheet2 Like

VLOOKUP(A1,Sheet2!a2:b600,2,false)
VLOOKUP(A1,Sheet2!a2:c600,3,false)
VLOOKUP(A1,Sheet2!a2:d600,4,false)
VLOOKUP(A1,Sheet2!a2:e600,5,false)
And so on

Thanks in Advance

Hardeep kanwar

Hi,

Just a little modification to your original formula:

=VLOOKUP(A$1,Sheet2!a$2:b$600,Row(A2),false)

copy this formula down.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Hardeep kanwar" wrote:

Hi! All

I have data in Sheet 1like

Its a code

5848606
9659285
8100949
4846771
7201303

And in Sheet2 i have master Sheet. Data Range A1:Z600

Now i want to Pickup the Data From Sheet 2 i.e B2 to Z2 to Sheet1
I use VLOOKUP(A1,Sheet2!a2:b600,2,false)
It Work Fine for me .

Is it possible to use only one Formula

Or i have to type one by one Vlookup Formula to pickup data from column in
sheet2 Like

VLOOKUP(A1,Sheet2!a2:b600,2,false)
VLOOKUP(A1,Sheet2!a2:c600,3,false)
VLOOKUP(A1,Sheet2!a2:d600,4,false)
VLOOKUP(A1,Sheet2!a2:e600,5,false)
And so on

Thanks in Advance

Hardeep kanwar

Thanks both of your Function Works fine

But when i drag to Down it show the Same Data,

I want a Function that pickup the Data from Right as well as Down.

Thanks in Advance.

Hardeep kanwar

"Shane Devenshire" wrote:

Hi,

Just a little modification to your original formula:

=VLOOKUP(A$1,Sheet2!a$2:b$600,Row(A2),false)

copy this formula down.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Hardeep kanwar" wrote:

Hi! All

I have data in Sheet 1like

Its a code

5848606
9659285
8100949
4846771
7201303

And in Sheet2 i have master Sheet. Data Range A1:Z600

Now i want to Pickup the Data From Sheet 2 i.e B2 to Z2 to Sheet1
I use VLOOKUP(A1,Sheet2!a2:b600,2,false)
It Work Fine for me .

Is it possible to use only one Formula

Or i have to type one by one Vlookup Formula to pickup data from column in
sheet2 Like

VLOOKUP(A1,Sheet2!a2:b600,2,false)
VLOOKUP(A1,Sheet2!a2:c600,3,false)
VLOOKUP(A1,Sheet2!a2:d600,4,false)
VLOOKUP(A1,Sheet2!a2:e600,5,false)
And so on

Thanks in Advance

Hardeep kanwar