Help with VLookup to populate data from one sheet to another based on a match in same-name column
I have an account database with facility location address information. When the same facility is entered in the order sheet, I need a lookup function to reference the database sheet and then populate the address.
This is what I have so far but I get a # NO MATCH error
=INDEX({Customer Database Range 1}, MATCH([Building / Facility]@row, {Customer Database Range 2}, 0))
Answers
-
Hey @stkatch
Your syntax for the INDEX/MATCH appears correct. With the generically named cross sheet ranges, I can't tell what your ranges are. (You can change the range names to reflect your actual column names).
Is it possible that your Range 1 and Range 2 are in the wrong place?
The INDEX range should be the address range. The Match range should be your Facility range. Are these the order of your ranges? To trouble shoot, delete Range 1 completely from the formula and re-insert it by going to the referenced sheet and insert the reference. Then, completely delete range 2 from the formula and reinsert it by going to the referenced sheet and insert the reference. As you are re-inserting each range, make sure that column is the correct column and that the entire column is selected.
And last trouble-shooting questions- the [Building / Facility]@row for the row in question is not blank and (2) the [Building / Facility]@row does actually have a match in the other sheet?
Kelly
-
THANK YOU KELLY!!! You saved me hours....swapping the ranges worked
=INDEX({Customer Database Range 2}, MATCH([Building / Facility]@row, {Customer Database Range 1}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!