Applying a vlookup formula to an entire column

[Dio]

Hello. I've got a 3000 column spreadsheet where I'm trying to match
fields from an existing set of informationl.

=VLOOKUP(A2,F2:I1997,2,FALSE)

Above is the formula I'm using to search an existing section of
information (F2:I1997) and comparing it to information in column A. I
want my results to show up in column b.

When I let Excel autofill the formula, It changes my table_array
(F2:I1997) as well as my lookup_value (A2). How do I change it so that
it just changes the lookup_value as it goes down without reentering it
3000 times?

Thanks for the help.

Dio.

 

[Don Guillett]

use absolutes for your lookup range
=VLOOKUP(A2,$F$2:$I$1997,2,FALSE)

 

[Dio]

So easy!

Thank you very much.

 

[Don Guillett]

Only if you know how.

 

[Dio]

Hmmm. When I first saw the response, I had already tried a workaround.
Now that I'm attempting it on a different spreadsheet, it's not
working.

My original formula looks like this:
=VLOOKUP(D1,'All Domains'!A12957,2,FALSE)

I'm pulling from another spreadsheet, so I have to have the bang
before the A already. When I try this:
=VLOOKUP(D1,'All Domains'!A!1:!D!2957,2,FALSE)

or this:
=VLOOKUP(D1,'All Domains'!A!1!2957,2,FALSE)

I get an error message and it won't let me use the formula. Is it
simply that you can't use absolutes inside of lookup formulas?

I guess the other option would be to name my range, but I've never
done that before. Anyone have any references for naming a range?

Thanks,
Dio.

 

[Dave Peterson]

The exclamation point goes after the worksheet name--not within the address of
the lookup range.

I'm not sure what you're trying to do, but maybe...

=VLOOKUP(D1,'some other sheet name here'!A12957,2,FALSE)

would work