If function help

[Pam]

I have a current formula that I need to nest with another IF formula....
=IFERROR(VLOOKUP(B15,D40:E42,2,FALSE),"Invalid Shipping option")

D40:E42 are shipping options with cost amounts that are input into cell c17

The iferror formula works, but I need to add a formula that when B15 is
blank then it will return a blank in cell C17

 

[Joe User]

The iferror formula works, but I need to add a formula
that when B15 is blank then it will return a blank in cell
C17

I confess that I do not have Excel 2007, so I cannot test this. But I see
no conceptional reason why the following would not work:

=IFERROR(IF(B15="", "", VLOOKUP(B15,D40:E42,2,FALSE)),
"Invalid Shipping option")

IFERROR simply returns the second parameter if there is an error in the
first parameter. Obviously, there is no error if B15 is blank and we provide
for that case in the first expression.

IFERROR might still be useful in this case if VLOOKUP might fail for other
reasons, e.g. B15 contains a value that does not match D4042.



----- original message -----

 

[Pete_UK]

Try this:

=IF(B15="","",IFERROR(VLOOKUP(B15,D40:E42,2,FALSE),"Invalid Shipping
option"))

Hope this helps.

Pete

 

[Pam]

Thank you both......they both worked!!!

Try this:

=IF(B15="","",IFERROR(VLOOKUP(B15,D40:E42,2,FALSE),"Invalid Shipping
option"))

Hope this helps.

Pete



.

 

[Pam]

Thank you both....each formula worked, thanks!

I confess that I do not have Excel 2007, so I cannot test this. But I see
no conceptional reason why the following would not work:

=IFERROR(IF(B15="", "", VLOOKUP(B15,D40:E42,2,FALSE)),
"Invalid Shipping option")

IFERROR simply returns the second parameter if there is an error in the
first parameter. Obviously, there is no error if B15 is blank and we provide
for that case in the first expression.

IFERROR might still be useful in this case if VLOOKUP might fail for other
reasons, e.g. B15 contains a value that does not match D4042.



----- original message -----

 

[Pete_UK]

You're welcome, Pam - thanks for feeding back.

Incidentally, I can't see Joe's post on Google Groups, although I can
see it in your other response.

Pete

 

[Joe User]

Off-topic....

Incidentally, I can't see Joe's post on Google Groups,
although I can see it in your other response.

Thanks for pointing this out.

This is a not-uncommmon problem with GG that I had observed long ago. It is
the reason why I abandoned GG some years ago.

It seems that some (but not all) initial articles and responses posted to
the MSDG server (using MS Discussion Groups the web interface) are not
picked up by GG. I don't know if it is limited to MSDG postings; they are
simply the only ones that I have noticed missing in GG.

I never understood why not. MSDG postings are properly pushed to the MSNews
server; and GG pulls microsoft.public.* postings from the MSNews server,
albeit indirectly through Giganews. Arguably, the fault might lie with
Giganews. But my money is on GG.

This time around, I see one obvious difference between Pam's and my MSDG
messages. Pam's message has a real-looking email address (although it is
actually a false one), namely (e-mail address removed), whereas the
email address in my message lacks a domain name, i.e. just joeu2004 instead
of (e-mail address removed) or the like. These are options in the MSDG
interface.

But I don't think that's a smoking gun. I notice that another one of my
MSDG postings does appear in GG. See
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/251c0a63500f2282.

Moreover, my recent postings to the MSNews server also have the
domainname-less "email address" of joeu2004. Those seem to appear in GG.
See
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/186882e71d8eccca.

(I really cannot say that with impunity, since I rarely check GG to see what
postings do and do not propagate to GG.)

In any case, GG users should be forewarned that they risk missing messages
posted through the MSDG web interface and perhaps other newsgroups
interfaces for some inexplicable reason.

PS: I usually post to the MSNews server, as I am doing now. But sometimes
I post to the MSDG server in response to an MSDG user in order to avoid the
"propagation" delay -- really a polling delay in the MSDG system -- which is
about 30-40 minutes lately.


----- original message ------

You're welcome, Pam - thanks for feeding back.

Incidentally, I can't see Joe's post on Google Groups, although I can
see it in your other response.

Pete