VLOOKUP Column Number Limit
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
Answers
-
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.
cheers,
Kelly
-
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.
-
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
-
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.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!