Index Match Between Sheets Working Sporadically

Dante N

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)

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