r/excel•
deviljo

Two-way Lookup (Index/Match/Match) with Duplicate Values

solved

Example: http://imgur.com/a/3xcOo

I'm working with a data table I'm not allowed to edit/consolidate in any way. Currently, I'm using a two-way lookup (via Index, Match, Match) to give me the value that intersects between a given row and column (see the link above). However, the problem with the table is that some of the row values are duplicated/non-unique (however, all the column headings are non-duplicated/unique). As such, I'm unable to correctly reference any second instance of a particular row instance. For instance, in the attached link, I'm unable to write a formula to get to the intersection of "Alpha" and "C", even though I can write one for "Alpha" and "B".

Can anyone help me solve this issue? Prefer to avoid array formulas here, if possible. Also, definitely an Excel novice here, so please bear with me :).

Posted Jun 26, 2017, 8:06 PM UTC

99
Loading comments…