vlookup
 

Hi,

I have a sheet with 52 columns and 250 rows. First column contains student's
name. I am using VLOOKUP function so that when I enter name of student, all
his data will be displayed in that row. For that I have to write VLOOKUP
formula for each cell in that row. But then I have to write parameter
"col_index_num" manually in each cell of that row. Is there any way so that
when I write formula for one cell, and after dragging it over the entire row,
"col_index_num" will change correspondingly for all cells in that row?

=vlookup(lookupvalue,lookuprange,column(),0)


"Pawan" wrote in message
...
Hi,

I have a sheet with 52 columns and 250 rows. First column contains
student's
name. I am using VLOOKUP function so that when I enter name of student,
all
his data will be displayed in that row. For that I have to write VLOOKUP
formula for each cell in that row. But then I have to write parameter
"col_index_num" manually in each cell of that row. Is there any way so
that
when I write formula for one cell, and after dragging it over the entire
row,
"col_index_num" will change correspondingly for all cells in that row?

if you start in Column A
=vlookup(name,datarange,column())

"Pawan" wrote:

Hi,

I have a sheet with 52 columns and 250 rows. First column contains student's
name. I am using VLOOKUP function so that when I enter name of student, all
his data will be displayed in that row. For that I have to write VLOOKUP
formula for each cell in that row. But then I have to write parameter
"col_index_num" manually in each cell of that row. Is there any way so that
when I write formula for one cell, and after dragging it over the entire row,
"col_index_num" will change correspondingly for all cells in that row?

Instead of using a digit to represent the column # you want, use COLUMN(),
which provides the column number for the cell in which it appears. For
example, if your formula is in col C (the 3rd column), and you want to get
data in the 2d column from the lookup table, use COLUMN()-1


"Pawan" wrote:

Hi,

I have a sheet with 52 columns and 250 rows. First column contains student's
name. I am using VLOOKUP function so that when I enter name of student, all
his data will be displayed in that row. For that I have to write VLOOKUP
formula for each cell in that row. But then I have to write parameter
"col_index_num" manually in each cell of that row. Is there any way so that
when I write formula for one cell, and after dragging it over the entire row,
"col_index_num" will change correspondingly for all cells in that row?

Pawan, unfortunately that's the only part of a LOOKUP that will not change by
autofilling. What you can do though, is to add an additional row on top of
your data, then modify your VLOOKUP to include a quick formula to add the
number from that new row into your Index Number.. So:

=VLOOKUP($A$1,Array,0+B1,FALSE)

This will modify your Index number to show you the sum of 0 and B1, so if
you enter a 1 in B1 your Index Number will show 1, once you autofill that
over and fill in your new row with 1,2,3,4,5,6,7,8,9...51 it'll pull the new
Index Number.

HTH,

Kevin M
MOS Excel Specialist

"Pawan" wrote:

Hi,

I have a sheet with 52 columns and 250 rows. First column contains student's
name. I am using VLOOKUP function so that when I enter name of student, all
his data will be displayed in that row. For that I have to write VLOOKUP
formula for each cell in that row. But then I have to write parameter
"col_index_num" manually in each cell of that row. Is there any way so that
when I write formula for one cell, and after dragging it over the entire row,
"col_index_num" will change correspondingly for all cells in that row?