Index Match Between Sheets Working Sporadically

Hello,

I am trying to make a currency conversion chart where if a user in Sheet 1, can choose a dropdown in [Column9] and it will index match term with Sheet 2, matching [Primary Column] and returning [Column2] back to Sheet 1 in [Column8].

Sheet 1

Sheet 2

The issue I am having is that the Index Match will sometimes not work unless it is on an unspecified row on Sheet 2.

As a test, I dragged the same formatting down on Sheet 2 to see if it would populate.

The index match picks up at the 8th iteration. If I delete the previous rows or move them around, the Index Match will stop working. Any ideas what the issue could be? This is the formula in Column8: =INDEX({Currency Conversion All}, MATCH([Column9]@row, {Currency}), 2)

Find more posts tagged with

Accepted answers

All comments

Dante N

=INDEX({Sheet 2 Primary Column-Column2}, MATCH([Column9]@row, {sheet 2 Primary Column-Column2}), 2)

Genevieve P.

Hi @Dante N

For your first range of the INDEX, make sure this is just one column, the Primary Column.

Then, you'll want to ensure you're using a 0 within the MATCH function to tell it to find an exact match without assuming any sort of Sort applied.

Try this:

=INDEX({Primary Column}, MATCH([Column9]@row, {Currency}, 0))

Note that because I only have one column selected there's no need to add a column index at the end. See: Formula combinations for cross sheet references. Let me know if that worked for you!

Cheers,

Genevieve

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.