Formula ?

[zone709]

Hi I actually need help on both of these. I just need to return blank if nothing is found. Right now it returns 0 if nothing is found and is kind of annoying thanks


=' Rates Table'!A68


=IFERROR(VLOOKUP($A$6:$A$208,' Rates Table'!$A$1:$D$506,4,0),"")

 

[estevaoba]

Try this:

=IF(VLOOKUP($A$6:$A$208,' Rates Table'!$A$1:$D$506,4,0)=0;"";IFERROR(VLOOKUP($A$6:$A$208,' Rates Table'!$A$1:$D$506,4,0),""))

 

[zone709]

Have an issue with this I think its around here 0)=0;"";



On the other one I figured it out this works below

=' Rates Table'!A68

<strike></strike>=IF(' Rates Table'!A24=0,TEXT(' Rates Table'!A24,""),' Rates Table'!A24)<strike></strike>

 

[Joe4]

Have an issue with this I think its around here 0)=0;"";

Note that in different versions of Excel, different argument separators are used. Some use semi-colons instead of commas. I think that may be what is going on with the response you received.
From your original post, it looks like you version uses commas, so try changing the formula estevaoba posted back to this:

[COLOR=#333333][COLOR=#333333]=IF([/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]VLOOKUP($A$6:$A$208,' Rates Table'!$A$1:$D$506,4,0)=0,"",[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]IFERROR(VLOOKUP($A$6:$A$208,' Rates Table'!$A$1:$D$506,4,0),""))[/COLOR][/COLOR]

 

[zone709]

hi thanks for the response and I am on 2016 excel maybe that's why. What about to add the text word also, because it help with the 0, but now I have #NA I tried =0,TEXT"" but didn't work?

 

[Joe4]

hi thanks for the response and I am on 2016 excel maybe that's why.

Shouldn't matter. When I said version, I am talking more about the region. Certain places in Europe and Asia use semi-colons as argument separators instead of commas.

Why do you have a whole range in your first argument in your VLOOKUP function?
It is typically just a single cell, as you are looking up one value at a time.
See: http://www.contextures.com/xlFunctions02.html

 

[zone709]

Yeah this formula is working for me for awhile now. Just have an issue with the 0 when pulling the formula down. I basically have a tab for the table of things I have. The formula looks on active sheet in column A to match name on the table tab. Then pull in the info from the right columns

 

[Joe4]

Just have an issue with the 0 when pulling the formula down

From your posts in this thread, it looks like that isn't the whole story. It looks like you have two things going on:
- the VLOOKUP returning 0 when you want it to return a blank
- the VLOOKUP returning #N/A, in which case you want it to a blank

Is that correct?

 

[zone709]

yes I got the 0 solved. I get the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NA]#NA [/URL] because I think if the column on active sheet in column A is empty ill get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NA]#NA [/URL]

 

[Joe4]

The #NA error results when it doesn't find any match.

I would try something like this:

[COLOR=#333333]=IF(A6="","",IF([/COLOR][COLOR=#333333]VLOOKUP(A6,' Rates Table'!$A$1:$D$506,4,0)=0,"",[/COLOR]VLOOKUP(A6,' Rates Table'!$A$1:$D$506,4,0)))