If two cells are blank then return blank, else vlookup cell with populated data

**Jason G** · 03-06-2017, 09:57 AM

Need a hand .. I'm stumped and don't know why:

Existing formula IF(ISBLANK(D4:D5)," ",IFERROR((IF(ISBLANK(D4),VLOOKUP(D5,'Master List'!B:O,14,0),VLOOKUP(D5,'Master List'!D:O,12,0))),"Not TSRd"))

1. If D4 and D5 are empty, then in D6 return blank
2. If d4 is not empty, vlookup d4 on master list and return TSR date in D6
3. if d5 is not empty, vlookup d5 on master list and return TSR date in D6
4. If D4 or D5, when entered are not found in the master list, return "Not TSRd"

Thank you in advance for your input!

**Kaper** · 03-06-2017, 10:06 AM

you cannot test more than one cell with ISBLANK at once. So either:

Formula:

Please Login or Register  to view this content.

or, instead of testing ISBLANK, just count non-blank cells, like:

Formula:

Please Login or Register  to view this content.

PS. I'd rather use empty string "" than a space " " when there is no input, but of course this is to some extend personal taste (and also how - if at all - the output cell is further processed)

**Jason G** · 03-06-2017, 10:10 AM

Kaper,

Thank you so much, I wasn't aware of that. I did think to use the "and" function; but didn't ... it's a relatively easy formula, I just had a brain fart this morning.

Thank you again!
~Jason

**loginjmor** · 03-06-2017, 10:12 AM

Hi -

I think your problem may be that ISBLANK is not a function that normally accepts arrays (D4:D5) as an argument. You can either make this an array formula by pressing Ctrl-Shift-Enter from the formula editing window to engage the array functionality. If you did it correctly, Excel will add curly braces {} to the formula. You will also have to unmerge the cells you are referencing (D4 and D5 are both merged across D, E, and F) and you will have to unmerge the cell the formula is in.

Just as a general practice, try not to reference merged cells from a formula. The results are sometimes unpredictable and you can spend a long time trying to figure out why it's not working when the answer is simply because you are referencing merged cells.

Hop this helps.

**Jason G** · 03-06-2017, 10:20 AM

Thank you for sharing your expertise and teaching me! I appreciate it!

If two cells are blank then return blank, else vlookup cell with populated data

LinkBack

Thread Tools

Rate This Thread

Display

If two cells are blank then return blank, else vlookup cell with populated data

Re: If two cells are blank then return blank, else vlookup cell with populated data

Re: If two cells are blank then return blank, else vlookup cell with populated data

Re: If two cells are blank then return blank, else vlookup cell with populated data

Re: If two cells are blank then return blank, else vlookup cell with populated data

Thread Information

Users Browsing this Thread

Similar Threads

How to Make Cells with Data Validation Lists Return Blank if a previous cell is Blank

[SOLVED] Cells in a range are all not blank, return maximum value (date), If 1 blank return 0

Return first non blank cell (cells have formulas that return blank)

Conditional Formatting - how to leave blank cells blank - no colour until date populated

Delete Cells with Specific Value and All Blank Cells Until Next Populated Cell is Checked

[SOLVED] Need formula to return blank instead of #N/A when source cell is blank, using VLOOKUP

Return Blank Cell if Sum Cells Blank or Zero

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions