conditional extraction of text values from a list

[mischge]

hi!

i have a formula that can extract unique text values from a list, based on a condition. the text values can occur several times in the list and each time the condition value is different.
i need the formula to extract each text value, where the condition holds true and then also show the condition value next to it.

sample data is attached.

any input is highly appreciated!

thank you!

[Richard Buttrey]

Hi,

I see no condition (apart from the IFERROR test) that is limiting the extract. But in any case why are you using a VLOOKUP when you could just use a simple Advanced Data Filter, driven by a single line macro if necessary.

[ramananhrm]

Please let me know if you are expecting this result.

[mischge]

Hi,

I see no condition (apart from the IFERROR test) that is limiting the extract.

=IFERROR(INDEX(Table1[Client];SMALL(IF(Table1[Total Overdue]>0;ROW(Data!$N$2:$N$10)-ROW(S.O.S.!$A$2)+1);ROWS(S.O.S.!$A$1:A1)));"")

[mischge]

hi ramananhrm

your formula seems to skip the text values that appear in the list multiple times.

using my sample sheet, i would want 'argentina' to be extracted three times (in three lines), each time showing the amount overdue next to it (in the appropriate column).

[Richard Buttrey]

Sorry I missed the Overdue test.

However my Q. still stands.

Why are you using formulae when an Advanced Filter seems more appropriate?
Simply create a criteria range for the Total Overdue label which contains ">0"

[mischge]

richard - you're quite right! it's a 'nice to have', not an absolute 'must have'...
i have quite a few extra columns in my workbook that dont need to be on the extracted list. hence i thought i'd try to figure this one out