INDEX+MATCH+COUNTIF with Criteria

[VBAMePlease]

Using INDEX+MATCH+COUNTIF to return unique values; I can't figure out where to add an argument that filters the range to look unique values that have an associated ID.

Example:

Col A has values A,B,C,D,E,F & G

Col B has values 1 through 20

A normal INDEX+MATCH+COUNTIF using Col B as my INDEX Range would return values 1-20.

But if I only want to find unique values for value "G" in Col A, how would I have it do this?

Assuming that 18-20 were the only numbers with "G" in Col A, the function would return 18,19,20.

Help much appreciated!

 

[Weazel]

maybe something like...

D2=IFERROR(SMALL(IF(FREQUENCY(IF($A$1:$A$19=$D$1,$B$1:$B$19),$B$1:$B$19),$B$1:$B$19),ROWS($D$2:D2)),"") Control Shift Enter

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64, align: right"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]g[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]f[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]g[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]a[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]b[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]c[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]d[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]g[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]g[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]g[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]g[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]b[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]c[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]d[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]e[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[VBAMePlease]

Returns #N/A, here is the formula I've tried using:

=INDEX(RAW_VDER!$C$142:$C$366,MATCH(Inputs!$E$2,INDEX(COUNTIFS($K$1:K1,RAW_VDER!$B$142:$B$366),0,0),0))

Inputs!E2 is my lookup value that must be matched in RAW_VDER!B142:B366

RAW_VDER!C142:C366 is the range in which unique values are located.

Both the values in Col B & C are text as well.