Subset Excel rows based on column values using vlookup

1
votes

I have a workbook:

sheet1:
id name ...(100 columns)
1  a
2  b
3  c
...
(about 300,000 rows)
sheet2:
id
3
4
...
(about 50,000 rows)

I want to select rows in sheet1 with id in sheet2, I tried using vlookup in sheet2:

VLOOKUP($A2, sheet1!$A:$CV, 1)

the problem is that I have 100 columns, autofill the cell horizontally doesn't automatically add the last parameter in vlookup, i.e., I have to manually change it to 2,3,...,100. Is there any better way to do this?

1
excelvlookup

1 Answers

0
votes

Add a helper row above your table with values 2, 3, 4, ... , 100. Say it will be row 1 so your destination table shifts down one row.

Then your VLOOKUP formula is like this:

=VLOOKUP($A3,sheet1!$A:$CV,B$1,0)

So you can specify the resulting column for VLOOKUP dynamically! Voila!

Variant 2 (without helper row):

=VLOOKUP($A2,sheet1!$A:$CV,COLUMN(),0)

So as you see, COLUMN() function returns the number of column your formula is typed into. Note: this formula will only work if you start writing it in destination table from column 2:2 on your worksheet. Otherwise you will need to apply offset like this (for example, if you start writing formula from column 5:5):

=VLOOKUP($A2,sheet1!$A:$CV,COLUMN()-3,0)