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

[sbrown2600]

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.

[PYS]

Hi

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

Regards

[sbrown2600]

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

[Lupp]

@PYS: “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

[PYS]

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

Regards