Ask Your Question

Can I have vlookup use a different column for the search vector, or get a subrange from a given range?

asked 2016-03-03 15:23:24 +0200

sbrown2600 gravatar image

I have a range called Data with 3 columns and 100 rows. I have a few different vlookups that use it as the lookup table. Problem is, some search the first column and some the second. I'm trying not to define a new named range, or otherwise hardcode the range that excludes the first column anywhere. So I would like to either:

1) Tell vlookup to search in the second column, rather than the normal first column or 2) Pass vlookup a subrange of Data, something like SUBRANGE(Data; 2:4, 1:100)

Is there a way to do this with any ease? I know that I could stitch together something with the Match function, but that's kinda ugly and I'd prefer a cleaner way.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-03-03 16:27:30 +0200

pierre-yves samyn gravatar image

updated 2016-03-03 16:31:13 +0200


You can use something like =VLOOKUP(A1;OFFSET(Data;0;1);2;0) to search the second column and get data from the 3rd.

This may be subjective but IMHO that's this kind of formula that is ugly. I always use MATCH :)

See: Vlookup.ods


edit flag offensive delete link more


Thanks, that's exactly what I was looking for! I consider this cleaner, because it looks simpler and shorter; with match, I would have to have to have a formula for what should be just a number, which isn't as simple as just modifying a range. But to each his own :)

sbrown2600 gravatar imagesbrown2600 ( 2016-03-03 16:41:27 +0200 )edit

answered 2016-03-03 19:05:26 +0200

Lupp gravatar image

@pierre-yves samyn: "This may be subjective but IMHO that's this kind of formula that is ugly. I always use MATCH :)" You too? I myself even tend to omit the ":)".

I "stitched" a little demonstration showing a few adavantages of using MATCH in combination with INDEX and / or OFFSET. (You also had to use OFFSET.)

In specific this allows for enhancements VLOOKUP is not capable of. Of course we may delay the transition to "slightly" more complicated formuale till we actually feel need of their advantages. See attached.ask65540AdvancedVlookup001.ods

edit flag offensive delete link more


Hi @Lupp

I am aware of these benefits. There are others such as the ability to mix horizontal / vertical, do not pass an entire table where it needs only one search column and result and so (your comment is probably more for sbrown2600). I try to get the message here, in FAQ, videos, social networks, training...

Please do, do not lose your :)


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-03-04 08:57:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-03-03 15:23:24 +0200

Seen: 307 times

Last updated: Mar 03 '16