Extracting multiple unique values from a table based on a single criterion.

Hey all,

I have seen similar problems to mine and I know I would have to use a mix of INDEX, MATCH and COUNTIF functions but unfortunately I just cannot get my head around the correct formulae.

I have attached a spreadsheet with the database and 2 samples with examples of input and output.

Thanks,
Al

Put this formula in cell J4:

=IF(F4=$D$17,MAX(J$3:J3)+1,"-")

and copy it down to the bottom of your data (or beyond if you want to add new data). Then you can use this formula in cell C20:

=IFERROR(INDEX(C:C,MATCH(ROWS($1:1),$J:$J,0)),"")

Copy this across into D20:F20, but then amend the formula in F20 to this:

=IFERROR(INDEX(I:I,MATCH(ROWS($1:1),$J:$J,0)),"")

and then you can copy the formulae in D20:F20 down as far as you need to.

Then, when you change the value in D17 the display will automatically change.

Hope this helps.

Pete

Thanks Pete, that is a nice solution.

Unfortunately I need both samples to work at the same time, otherwise I would have to do a lot of manual workarounds in my original spreadsheet.

Cheers,
Al

Well, you could use a second helper column, with this formula in K4:

=IF(F4=$D$26,MAX(K$3:K3)+1,"-")

and this formula in C29:

=IFERROR(INDEX(C:C,MATCH(ROWS($1:1),$K:$K,0)),"")

as the attached file demonstrates.

Hope this helps.

Pete