vlookup error

Andrew V

Hello,

I have created a vlookup that works but cannot seem to create a second one. 

Working: =VLOOKUP([Currency Code]55, {Customer FY18 FX Rates Range 2}, 4, false)

Not working: =VLOOKUP(Country55, {Region List Range 2}, 4, false)

I cannot see why one is working and the other is not. Any help or advice appreciated.

Chris McKay

Hi Andrew,

Without access to your data to see what columns/ranges you are referencing, it will be difficult to diagnose.

I'm of the opinion that VLOOKUP & HLOOKUP should be deprecated. The fact that the columns need to be in order (e.g. what you're looking up must be the leftmost column and what you want to return has to be to the right) and we need 2 different functions to look horizontally (not in Smartsheet) or vertically is just painful.

Lookups also tend to be slow in most applications (I'm assuming Smartsheet is no different) once you have a larger dataset.

Consider using INDEX & MATCH together as it will search anywhere in a range for a value regardless of where it appears and it also means no more messing around with silly column numbers:

=INDEX(lookup_range, MATCH(search_value, search_range, [match_type]))

So your first formula would look something like this:

=INDEX([Column 4 in your VLOOKUP], MATCH([Currency Code]55, {Customer FY18 FX Rates Range 2}, 0))

This means you can name ranges for what you're looking for. The ranges can overlap (so what you're looking to return can be a range in the middle of another range you're searching) and will move if your target sheet is changed (i.e. if VLOOKUP column 4 suddenly becomes column 6).