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
- 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!