VLOOKUP lookup value range workaround - If statement?

[Please_Please_Help_Me]

Hello,

My understanding of VLOOKUP function is that you cannot insert a range of cells as the lookup value. So, I am trying to find a workaround that only outputs data if the value in a 'lookup range' is not blank.

Please see the attached original_data.jpg.

I would like to return Customer Names and Account Numbers if the values in the Date column are not blank. However, I do not want the number of rows in the output to equal those of the original data (shown in not_ideal_outcome.jpg). I was able to create that with an if statement. Instead, I need to avoid blank rows in the output (shown in ideal_outcome.jpg).

I am trying to automate the process of manually deleting blank output rows because I need this for a macro that will be used on an excel file that constantly changes.

Thank you!

 

[Scott T]

Since you have 365 you can use filter

Book1
	A	B	C	D	E	F	G
1	date	cust name	acc number		date	cust name	acc number
2	10/7/2018	alice	654		10/7/2018	alice	654
3		jane	4894		11/20/2019	bill	3212
4	11/20/2019	bill	3212		11/21/2019	Tracy	13
5		Harold	459
6		Ben	7898
7	11/21/2019	Tracy	13
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=FILTER(A2:C7,A2:A7<>"")
Dynamic array formulas.

 

[Please_Please_Help_Me]

Since you have 365 you can use filter

Book1
	A	B	C	D	E	F	G
1	date	cust name	acc number		date	cust name	acc number
2	10/7/2018	alice	654		10/7/2018	alice	654
3		jane	4894		11/20/2019	bill	3212
4	11/20/2019	bill	3212		11/21/2019	Tracy	13
5		Harold	459
6		Ben	7898
7	11/21/2019	Tracy	13
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=FILTER(A2:C7,A2:A7<>"")
Dynamic array formulas.

Hi Scott,

Thank you so much. This is exactly what I asked for.

Can I throw one more condition in this situation? In my data, there are two different date columns. Can I use the 'filter' function to return the dates, cust name, and acc number if either date column is not blank? I tried using the 'or' function in the second argument of the 'filter' function but was unsuccessful.

 

[Fluff]

How about

=FILTER(A2:D7,(A2:A7<>"")+(B2:B7<>""))

 

[Please_Please_Help_Me]

This is great. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.