Click here for important information about Computing.net.

Solved VLOOKUP to return Enter BS if feeder empty, else value

January 27, 2021 at 10:30:46
Specs: Windows 10
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.


See More: VLOOKUP to return Enter BS if feeder empty, else value


#1
January 27, 2021 at 11:17:20
✔ Best Answer
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


Reply ↓  Report •

#2
January 27, 2021 at 11:51:44
That worked. Same with IFERROR failed. Don't understand, but got it now.

Reply ↓  Report •

#3
January 27, 2021 at 13:10:41
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.


Reply ↓  Report •

Related Solutions

#4
January 29, 2021 at 13:19:18
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.


Reply ↓  Report •

Ask Question