VLOOKUP lookup value range workaround - If statement?

Joined
Aug 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • original_data.JPG
    original_data.JPG
    18.6 KB · Views: 11
  • not_ideal_outcome.JPG
    not_ideal_outcome.JPG
    15.2 KB · Views: 11
  • ideal_outcome.JPG
    ideal_outcome.JPG
    13.3 KB · Views: 11

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Since you have 365 you can use filter

Book1
ABCDEFG
1datecust nameacc numberdatecust nameacc number
210/7/2018alice65410/7/2018alice654
3jane489411/20/2019bill3212
411/20/2019bill321211/21/2019Tracy13
5Harold459
6Ben7898
711/21/2019Tracy13
Sheet1
Cell Formulas
RangeFormula
E2:G4E2=FILTER(A2:C7,A2:A7<>"")
Dynamic array formulas.
 
Upvote 0
Solution
Since you have 365 you can use filter

Book1
ABCDEFG
1datecust nameacc numberdatecust nameacc number
210/7/2018alice65410/7/2018alice654
3jane489411/20/2019bill3212
411/20/2019bill321211/21/2019Tracy13
5Harold459
6Ben7898
711/21/2019Tracy13
Sheet1
Cell Formulas
RangeFormula
E2:G4E2=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.
 

Attachments

  • new_ideal_outcome.JPG
    new_ideal_outcome.JPG
    20.3 KB · Views: 5
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top