# VLOOKUP if cell is empty return specific text instead of 0

#### lunatu

##### Board Regular
Hi,

is it possible with vlookup formula to return specific text if the cell is empty? I tried this formula =IFERROR(VLOOKUP(E2;'[Workbook1]Sheet1'!B\$2:N\$4000;7;FALSE);"Open") but it brings value 0 if the cell is empty (i would like it to return text "Open").

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
What sort of values is the vlookup returning, text or numbers?

Try this:

=IF(VLOOKUP(E2;'[Workbook1]Sheet1'!B\$2:N\$4000;7;FALSE)="";"Open";VLOOKUP(E2;'[Workbook1]Sheet1'!B\$2:N\$4000;7;FALSE))

Try this:

=IF(VLOOKUP(E2;'[Workbook1]Sheet1'!B\$2:N\$4000;7;FALSE)="";"Open";VLOOKUP(E2;'[Workbook1]Sheet1'!B\$2:N\$4000;7;FALSE))
Awesome, this is working perfectly! Thanks

What sort of values is the vlookup returning, text or numbers?
I got solution below

There maybe a simpler solution depending on what type of data is being returned, which is why I asked.

Hello @Fluff
I would like to know another formula except in post #3 for the same situation
what would be that?

Depends on what sort of data is being returned.

if cell is blank in returned data so instead of showing 0 we want blank or any text then?

That does not answer my question & only the OP can tell us that.

Replies
6
Views
116
Replies
3
Views
100
Replies
2
Views
115
Replies
1
Views
86
Replies
7
Views
269

1,196,272
Messages
6,014,375
Members
441,817
Latest member
biRosETa

### 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.

### Which adblocker are you using?

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

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