Help with excel formulas

Were_Doomed

Sandtree said:

Were_Doomed said:

Sandtree said:

Were_Doomed said:

If there is no corresponding value returned by the VLOOKUP you'll get a #N/A result. You can handle this by preceding the VLOOKUP formula by using:

=IF(ISNA(VLOOKUP(etc.)),"The value you want to show for a #N/A result",VLOOKUP(etc.))

This is how most people write the above but I wonder why the below formula isnt more commonly used? In principle it feels as if it should be more efficient....

=IFERROR(VLOOKUP(etc),"The value you want to show for a #N/A result")

That should also work, but is actually a few more characters to type/copy.  

Its less
if(isna() -v- iferror( = 1 character save
,vlookup(etc) -v- '' = 13 or more characters saved

I see what you mean now ... IFERROR only requires two elements - the value desired, and the value to display if an error is returned. (I've never used it before, so thanks for the school day).  

Heedtheadvice

I feel one of the points I was making was missed.

As was written in a subsequent post vlookup returns the first match in the list. I presume here that having two identical entries is a data entry error, thus picking the first could therefore be incorrect and is an error -not an Excel error but a potential erroneous result.

Further error trapping is desirable (in this case to find duplicates) such as sorting the list, inspecting it or highlighting equality in sequential items.

That is only one example of an unforseen error that may not even be obvious in use. I believe considering what these might be and planning for them in a design of a workbook to be as important, may, more important than those errors that become obvious! Remember the missing covid records......?

WaywardDriver

Heedtheadvice said:

I feel one of the points I was making was missed.

As was written in a subsequent post vlookup returns the first match in the list. I presume here that having two identical entries is a data entry error, thus picking the first could therefore be incorrect and is an error -not an Excel error but a potential erroneous result.

Further error trapping is desirable (in this case to find duplicates) such as sorting the list, inspecting it or highlighting equality in sequential items.

That is only one example of an unforseen error that may not even be obvious in use. I believe considering what these might be and planning for them in a design of a workbook to be as important, may, more important than those errors that become obvious! Remember the missing covid records......?

You're correct of course. But much depends on the purpose of the workbook. Is it a simple hobby project for the OP or to be used by non-specialists in a vital project?
Also how the text values are entered - manually or imported from another system?