Trying to surround a vlookup with iferror

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
I updated my vlookup formula to the following so that blank cells (aka date format 1/0/1900) in column 6 return a blank cell.

=IF(LEN(VLOOKUP($I6,$B$5:$G$49,6,0))=0,"",VLOOKUP(I6,$B$5:$G$49,6,0))

It works when there is a value to lookup. But as expected, I get a N/A error when cells in the I column are blank.

Tried adding a IFERROR formula around it but I'm doing it wrong somehow. Please advise.

=IFERROR((LEN(VLOOKUP(I6,$B$5:$G$49,6,0))=0,"",VLOOKUP(I6,$B$5:$G$49,6,0)),"")

I have other if error functions in my spreadsheet that work just fine...
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,932
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
How about

=IFERROR(IF(LEN(VLOOKUP($I6,$B$5:$G$49,6,0))=0,"",VLOOKUP(I6,$B$5:$G$49,6,0)),"")
 

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
How about

=IFERROR(IF(LEN(VLOOKUP($I6,$B$5:$G$49,6,0))=0,"",VLOOKUP(I6,$B$5:$G$49,6,0)),"")


Sure enough, that did it. For some reason, i thought I would be replacing the existing IF statement, thinking that by not keeping the existing IF in there, it would automatically default to IF, somehow. I don't know.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,186
Messages
5,570,744
Members
412,338
Latest member
ebf6543
Top