CODEWITHSUNDEEP
excelif-statementexcel-formulavlookup
Samuel Ellett
Samuel Ellett

Reputation: 37

If and Vlookup across range in Excel

Essentially, I need to work out for each unique ID(Column A), whether or not a "Y" exists in a set range of cells for that unique ID (Column $D). Some ID's are only there once (where i would just use a vlookup) but other ID's are repeated with different values in the return column.

Ideally i'd like the output sheet to look like Output sheet. Where each id is grouped (no duplicate id's).

Raw Sheet

Raw Sheet

Output sheet

Output sheet

Please let me know if i've structured this incorectly or any other feedback. Much appreciated,

Sam

Upvotes: 1

Views: 150

Answers (1)

EoinS
EoinS

Reputation: 5482

In your output sheet you could try countifs. The first test would count the ID in raw data, the second would check if there are any Y's:

=if(countifs( 'Raw Sheet'A:A, A1, 'Raw Sheet'!D:D,"Y")>0, "Y","N")

Countifs looks for rows that satisfy having both a matched ID in column A and at least one "Y" in column D. If there are one or more matches, the if condition returns "Y" otherwise "N".

Let me know if you need help removing duplicates from your raw data column A.

Upvotes: 1

Related Questions