Search code examples
google-sheetsfiltergoogle-sheets-formulaarray-formulasgs-vlookup

Filter column with condition Google Sheets

I'm using Filter function using Google Sheets but can't use it the way I want, it has been 3 days...

Basically I have Sheet 1 with a column "e-mail" and column "Lead ID". The Sheet 2 has the same "Lead ID", but it's filtered. Meaning, Sheet 1 it' sequential with 1,2,3,4,5...and sheet 2 it's not, it's like 2,4,5,23,41... What I want to to find the right e-mail address that's in Sheet 1, that has the same Lead ID in both. I've used Filter function which works really well because it updated the rows and I dont need to drag the cell. But in this case this is not working:

ABD!C:C it's the E-mail Column. ABD!T:T it's the Lead ID Column Sheet 1 A:A It's Lead ID Column Sheet 2.

=FILTER(ABD!C:C,ABD!T:T=A:A)

It returns the name of the column (e-mail) and not the actual e-mail address. If I use A2 instead of the column A:A, it works. But I can't use that way because I need to drag that cell and that causes a problem. I need to use this as the filter works which doesn't require to drag the cell.

=FILTER(ABD!C:C,ABD!T:T=A2)

https://docs.google.com/spreadsheets/d/1MKXCAc1-d8OU008OEHI2Tu7AObvaOueUD30jl5iG6W8/edit?usp=sharing

Any advice?

Solution

  • use:

    =ARRAYFORMULA(IFNA(VLOOKUP(A2:A, {ABD!T:T, ABD!B:B}, 2, 0)))

    0