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

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

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

stkatch

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.