VLOOKUP lookup values as range

  • Thread starter Thread starter Andy Brown
  • Start date Start date
A

Andy Brown

I'm working on a file for someone. It's full of VLOOKUPs where lookup value
is *a range*. Simplified example ;

A1:A10 = 1-10, as does D1:D10. E1:E10 = assorted letters. In B1,

=VLOOKUP($A$1:$A$10,$D$1:$E$10,2,FALSE)

Copying B1 down duplicates the formula identically, obviously. Yet tracing
precedents for e.g. B4 does actually pick up A4 as first arg (?).

There are hundreds of these, and due to the file structure amending would be
a huge chore. Is there anything wrong with 1st arg being a range rather than
a single cell/value (not even sure how it still works) in whatever respect
(wb size, memory, calculation, etc.)?

TIA,
Andy
 
Hi
I would use
=VLOOKUP($A1,$D$1:$E$10,2,FALSE)
and copy this down. the row index will change automatically
 
I would use
=VLOOKUP($A1,$D$1:$E$10,2,FALSE)
Click to expand...

Thanks Frank, so would I. Unfortunately, the user has already constructed
them such as

=VLOOKUP($A$2:$A$791,'03-May'!A:C,3,FALSE)

, so they could be dragged down OK, but *not* across without a fair bit of
INDIRECT kludging or Find&Replace.

I was more wondering if it would do any harm to keep 1st arg as a range,
since it actually seems to return OK. The wb's A MESS, so if there's any
cleaning up I could skip, all the better.

(Reading Help, it's not so hard to see how users could misinterpret lookup
value definition).

Rgds,
Andy
 
Back
Top