vlookup

[Guest]

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?

 

[N Harkawat]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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