# How to return blank result if no match for VLOOKUP?

#### tkroper

##### Active Member
I'm using this formula (thanks to dafan & HalfAce http://www.mrexcel.com/forum/showthread.php?p=1604964&posted=1#post1604964)
Code:
``=IF(VLOOKUP(\$A177,\$A\$736:\$C\$787,3,FALSE)=0,"",VLOOKUP(\$A177,\$A\$736:\$I\$787,3,FALSE))``
to return the value located in the 3rd column of the range when the value in the 1st column of the range matches the data in A177.

However, there isn't a match for the data in A178 in the range, so the formula returns "#N/A". I would like the cell containing the formula to remain blank in those instances. Is there a way to do this?

Thank you very much for your help!

Todd

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### dafan

##### Well-known Member
Code:
``=IF(OR(VLOOKUP(\$A177,\$A\$736:\$C\$787,3,FALSE)=0,ISNA(VLOOKUP(\$A177,\$A\$736:\$C\$787,3,FALSE)=0)),"",VLOOKUP(\$A177,\$A\$736:\$I\$787,3,FALSE))``

You can test with IF(ISNA( < value > ) if a formula generates a #N/A.

#### tkroper

##### Active Member
Thank you dafan! I still get a #N/A result though.

I noticed I should have shown \$C\$787 instead of \$I\$787 and changed it, but that didn't make a difference.

If I follow, your "IF(ISNA" should make the formula think it's found a match. I wonder then if it's attempting to return the value in the 3rd column of the table but can't locate one.

#### dafan

##### Well-known Member
Sorry typo I think:
Code:
``=IF(OR(VLOOKUP(\$A177,\$A\$736:\$I\$787,3,FALSE)=0,ISNA(VLOOKUP(\$A177,\$A\$736:\$I\$787,3,FALSE))),"",VLOOKUP(\$A177,\$A\$736:\$I\$787,3,FALSE))``
</pre>

#### tkroper

##### Active Member
I appreciate all your help dafan - thank you very much!

#### njimack

##### Well-known Member
A shorter method would be:
=IF(COUNTIF(\$A\$736:\$A\$787),VLOOKUP(\$A177,\$A\$736:\$C\$787,3,FALSE),"")

#### tkroper

##### Active Member
A shorter method would be:
=IF(COUNTIF(\$A\$736:\$A\$787),VLOOKUP(\$A177,\$A\$736:\$C\$787,3,FALSE),"")

Thank you njimack! I tried your solution, but got an error that there are too few arguments for this formula. I tried fiddling around with it but I'm not knowledgeable enough with formulas to correct it.

#### spcalan

##### Well-known Member
What about adding an If(iserrr( to the beginning?

#### tkroper

##### Active Member
I couldn't stop fiddling, and came up with this:
Code:
``=IF(ISNA(VLOOKUP(\$A178,\$A\$736:\$C\$787,3,FALSE)),"",VLOOKUP(\$A178,\$A\$736:\$C\$787,3,FALSE))``

It seems to do the trick. Thank you very much for helping me through this one njimack!

#### njimack

##### Well-known Member
That's still not very efficient, since you're performing the VLOOKUP twice. There was a missing argument in my previous post. Try the following:

=IF(COUNTIF(\$A\$736:\$A\$787,\$A177),VLOOKUP(\$A177,\$A\$736:\$C\$787,3,FALSE),"")

I couldn't stop fiddling, and came up with this:
Code:
``=IF(ISNA(VLOOKUP(\$A178,\$A\$736:\$C\$787,3,FALSE)),"",VLOOKUP(\$A178,\$A\$736:\$C\$787,3,FALSE))``

It seems to do the trick. Thank you very much for helping me through this one njimack!

Replies
7
Views
471
Replies
8
Views
376
Replies
2
Views
170
Replies
3
Views
337
Replies
5
Views
203

1,191,500
Messages
5,986,921
Members
440,065
Latest member
JCH136

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

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