#N/A Replacement

[Susana C via OfficeKB.com]

I searched a the "Correct #N/A Error" threads and found some info on isna and
iserror, but I must be doing something wrong. For any item that is not found
in the following lookup, I would like the "COR" to be returned. How do I do
this? I am using the ,0 at the end in liu of "false" because "false" does
not work when you have multiple active filters working.

=VLOOKUP($D19,'[Fee File.xls]Fees'!$C$13:$H$20108,1,0)

Thank you!

 

[Dave Peterson]

I don't understand your comment about 0 and false. They are equivalent. And
=vlookup() looks at the whole range--not just the visible cells (in a filtered
table).

But since you're trying to return the first column, it looks like you're just
looking to see if the lookup value is in the first column in that lookup table.

You could use this instead:

=if(iserror(match($d19,'[Fee File.xls]Fees'!$C$13:$C$20108,0)),"COR",$d19)

If you include all of C13:H20108 in your formula, then any change to D13:H20108
will cause your formula to recalculate. By limiting it to just C13:c20108, your
worksheet may recalculate quicker.

I searched a the "Correct #N/A Error" threads and found some info on isna and
iserror, but I must be doing something wrong. For any item that is not found
in the following lookup, I would like the "COR" to be returned. How do I do
this? I am using the ,0 at the end in liu of "false" because "false" does
not work when you have multiple active filters working.

=VLOOKUP($D19,'[Fee File.xls]Fees'!$C$13:$H$20108,1,0)

Thank you!