Click here for important information about Computing.net.

My VLOOKUP formula works well (Excel2010), but static return #N/A from blank cells confuses users. =VLOOKUP(G11,'eAG to A1c Table'!$T$4:$U$114,2)

I average the G Column, and use this formula to only use # >0 for returning a true average.

=AVERAGEIF(G3:G32,">0",G3:G32)

Desire the lookup formula to return "Enter BS" if G cell is empty. (G is formatted as # no decimal).

Can get a value of "Enter BS", but then when a # is entered in G, the "Enter BS" remains.

Have tried many variations of IFERROR, but the logic escapes me.

IFERROR method:

Syntax:=IFERROR (value, value_if_error)

Arguments:value - The value, reference, or formula to check for an error.

value_if_error - The value to return if an error is found.

Formula:=IFERROR(VLOOKUP(G11,'eAG to A1c Table'!$T$4:$U$114,2),"Enter BS")

Old School Method using ISNA:(Used before IFERROR function was introduced)

=IF(ISNA(VLOOKUP(G11,'eAG to A1c Table'!$T$4:$U$114,2)),"Enter BS",VLOOKUP(G11,'eAG to A1c Table'!$T$4:$U$114,2))

message edited by DerbyDad03

I don't understand. re:

"That worked"?What worked? I offered 2 solutions, so saying "that worked" doesn't tell us much.

re: "Same with IFERROR failed"

Same what?

FWIW, both of my posted suggestions work exactly the same in my test workbook.

If something still isn't working for you, try the Evaluate Formula feature on the Formulas ribbon to step though the formulas.

The "old school method using ISNA" formula did exactly what I wanted to accomplish". "Same with IFERROR failed", referred to my use of what appeared to be the same formula except for your =IF(ISNA(... , solution, i.e. "old school method using ISNA vice IFERROR".

Resultantly, I made no additional attempt to figure out why my =IFERROR version failed to work.

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History