2

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Renato Pimpão
  • 135
  • 2
  • 11

1 Answers1

3

use:

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

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I would place this in `D1`, so the first row with data could be deleted without consequences: `={"Email"; ARRAYFORMULA(IFNA(VLOOKUP(A2:A, {ABD!T:T, ABD!B:B}, 2, 0)))}` – kishkin Mar 24 '20 at 12:52