Conditioned index/match or vlookup

[chococ]

Hello all,

I have a seemingly simple problem here, I have a column of journal number duplicated multiple times and I would like to label the row as "keep" if *one of the* results from the next column matches a specific value.

So for example, journal SIN0001 might have COS, VAT and AP in the next column, and I would like to label all 3 rows of SIN0001 as "keep" even if only one of the rows return "AP". In other words I want to filter out all journal numbers that contains "AP".

Attached is a sample file, "Source" sheet is the sheet I need a formula to go in the yellow cells, whereas "ideal outcome" sheet is the, well, ideal outcome I am looking for.

Sample1.xlsx

Any help would be greatly appreciated and thank you very much in advance!

[José Augusto]

Try this array formula

=IFERROR(IF(MATCH(A2&"AP",Source!$A$2:$A$61&Source!$B$2:$B$61,0)>0,"Keep","Delete"),"Delete")

Please Login or Register  to view this content.

see the file Sample1.xlsx

[chococ]

Thanks a lot, that's perfect!!