Why isn't my VLOOKUP Formula returning a match?
I've input a column formula to cross reference another sheet, and return the value in the 3rd column when there's a match. The formula is =VLOOKUP(Email@row, {Table with 3 data columns in the other sheet},3)
The formula worked for the first row of data, but the second returns "#NO MATCH. I'm really confused at to why this isn't working. I've tried to convert the column on the reference sheet to contact, text, and I've even tried linking the cells in the reference sheet to the column containing the search value in the VLOOKUP (Email column).
Any advice would be appreciated
Best Answer
-
Without seeing how your reference table is set-up I'm not sure if we can fully diagnose the VLOOKUP error. I would actually recommend that you start using INDEX(MATCH()) to avoid errors when columns/data are moved. The formula would look like:
=INDEX({Code Column},MATCH(Email@row,{Recipient Column},0))
If you've never used INDEX(MATCH() formulas, I'd definitely recommend doing some quick research online. There are a ton of resources out there and it will make a huge difference in your sheet formulas.
Answers
-
Update, I solved this with an INDEX/MATCH function instead. In the column where I want to return the value:
=INDEX({Column on Reference Sheet with Code},MATCH(Email@row,{Column on reference sheet with emails},0))
-
Without seeing how your reference table is set-up I'm not sure if we can fully diagnose the VLOOKUP error. I would actually recommend that you start using INDEX(MATCH()) to avoid errors when columns/data are moved. The formula would look like:
=INDEX({Code Column},MATCH(Email@row,{Recipient Column},0))
If you've never used INDEX(MATCH() formulas, I'd definitely recommend doing some quick research online. There are a ton of resources out there and it will make a huge difference in your sheet formulas.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!