CODEWITHSUNDEEP
excelvlookup
q0mlm
q0mlm

Reputation: 363

VLOOKUP (INDEX-MATCH) returning nth result, with n variable

I have the following two tables :

Table 1

Table 2

I want to retrieve from the second table the ID CODE, respecting the order for duplicated values, i.e. if [email protected] is first I want the first ID CODE, if second the second etc.

I have the formula INDEX-MATCH to get the nth value and it works fine:

{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

Here can be found a more detailed explanation: https://exceljet.net/formula/get-nth-match-with-index-match

My problem is that I don't have a fixed nth value, but it changes depending on the cardinal position of the duplicated value.

I am now using this formula with success:

IF(COUNTIF($A$2:$A2;$A2)=1; INDEX('Table2'!$E$1:$E$18;MATCH(A2; 'Table2'!$D$1:$D$18;0)); IF(COUNTIF($A$2:$A2;$A2)=2; INDEX('Table2'!$E$1:$E$18; SMALL(IF( 'Table2'!$D$1:$D$18=A2;ROW('Table2'!$D$1:$D$18)-ROW(INDEX('Table2'!$D$1:$D$18;1;1))+1);2)); IF(COUNTIF($A$2:$A2;$A2)=3; INDEX('Table2'!$E$1:$E$18; SMALL(IF( 'Table2'!$D$1:$D$18=A2;ROW('Table2'!$D$1:$D$18)-ROW(INDEX('Table2'!$D$1:$D$18;1;1))+1);3));"")))

Which basically makes use of mixed excel reference and translates to:

IF(COUNTIF(Dynamic Range, Mail Table 1 Value) = 1; Use INDEX-MATCH Formula with nth parameter set to 1; IF(COUNTIF(Dynamic Range, Mail Table 1 Value) = 2; Use INDEX-MATCH Formula with nth parameter set to 2;
IF(COUNTIF(Dynamic Range, Mail Table 1 Value) = 3; Use INDEX-MATCH Formula with nth parameter set to 3;""))

It works, however it is not elegant, it is difficult to maintain and more importantly I would have to extend it with nested IF ad infinitum to avoid any type of failure, so I would like to hear to more appropriate solutions to this problem.

I'll leave here the table with the expected results:

Table 3

Upvotes: 0

Views: 115

Answers (2)

q0mlm
q0mlm

Reputation: 363

As an alternative to @basic's answer and thanks to his intuition I will post here my formula simplified using COUNTIF($A$2:A2;A2) as parameter :

{=INDEX('Table 2'!$E$2:$E$18;SMALL(IF('Table2'!$D$1:$D$18=A2;ROW('Table2'!$D$1:$D$18)-ROW(INDEX('Table2'!$D$1:$D$18;1;1))+1);COUNTIF($A$2:$A2;A2)))}

Upvotes: 0

basic
basic

Reputation: 11968

Perhaps following formula can help you:

=INDEX($E$2:$E$18;AGGREGATE(15;6;(A2=$D$2:$D$18)/(A2=$D$2:$D$18)*ROW($D$2:$D$18)-1;COUNTIF($A$2:A2;A2)))

enter image description here

Upvotes: 1

Related Questions