VLOOKUP Column Number Limit

SYSPK

Hi,

Is there a limit to how many columns I can use for a VLOOKUP when referencing another sheet?

e.g. =VLOOKUP(source@row, {my other sheet}, 100, false)

Would this work? Can I go above 100?

Thank you

Kelly Moore

Hey @SYSPK

As an alternative to VLOOKUP, Index/Match is another lookup function. Because it doesn't require a structured arrangement of a table, Index/Match is more robust than VLOOKUP.

Note the cross sheet reference ranges are single columns.

=INDEX({Other sheet COLUMN of data you want (your column 100 of vlookup)}, MATCH([you called this source sheet]@row, {Other sheet COLUMN you're matching to (your column 1 of vlookup)}, 0)

The 0 is part of the match function and says you're looking at unsorted data.

https://help.smartsheet.com/function/match

https://help.smartsheet.com/function/index

cheers,

Kelly

SYSPK

Thanks @KDM . Are there any downsides to index/match that I should be aware of?

I liked the simplicity of VLOOKUP and would like to continue using it so long as I don't surpass the limit.

Kelly Moore

@SYSPK

No downsides that I have ever experienced. I like that the source data can be arranged freely with no worry of unintentionally affecting an existing lookup formula by the insertion or rearrangement of columns. There is no mis-counting of columns to find the desired data.

cheers

Kelly

Cemil Binlik

https://community.smartsheet.com/discussion/comment/278600#Comment_278600

Hey @Kelly Moore your =INDEX({Other sheet COLUMN of data you want (your column 100 of vlookup)}, MATCH([you called this source sheet]@row, {Other sheet COLUMN you're matching to (your column 1 of vlookup)}, 0) formula is missing a bracket at the end after 0, took me a while to catch it.

Kelly Moore

Hey @Cemil Binlik

Thanks for the catch but most of all, clarifying for others. Good job

Kelly