Use of nested IFERROR or other suitable functions

[kyjae]

Hi All,

I need some help in nesting IFERROR functions (or an alternative suggestion to tackling this particular problem). I’ve attached an example spread sheet to illustrate the issue.

So, we want to do an email-shot to members of a club using MS Word drawing upon an Excel spread sheet. In the ‘Emails’ tab in the attached example spread sheet, each member has their forename, surname and membership status recorded. We also join their names together to use a VLOOKUP function which I’ll explain shortly. In the ‘Emails’ tab, a person’s membership is recorded as ‘M’ for a current member or ‘X’ or blank if they are a previous or expired member. As you can see in this sheet, there is a deliberate typo against Joe Jones' name.

Moving on, in the ‘Membership_Mailshot’ tab, we record the person’s forename and surname and join these two together to allow a VLOOKUP to extract the correct email address against their name from the ‘Emails’ tab and we use the same to check their Membership status.
In the attached example spread sheet, Gladys' forename has a typo in the ‘Emails’ tab which correctly returns 2 x #N/A errors against her name in the ‘Membership_Mailshot’ tab. In this same sheet in Column A, I want to be able to check for multiple errors closely against each person so I can identify these quickly.

The live sheet from which this example is drawn has some 4,800 records of which 300 or so are non or expired members; furthermore, there are 30 or so columns inserted between the surname column and the co-join column which contain specific data to go into the mailshot based on other parameters that that particular member holds. Consequently, having a spread sheet with 24,000 odd cells, and the potential for typos in names, is tricky to scan for multiple VLOOKUP errors across rows, hence the need for a single column to flag this.

I’ve tried playing around with nested IFERROR functions to check for #N/A across multiple columns but I’m struggling with the correct syntax. I might even be using the wrong type of function to achieve this!

Apologies for the length of this post, but I wanted to be as clear as I could in the hope that someone could point me in the right direction.

Many thanks.

[oeldere]

Why do you want to check the names if you have the names in the worksheet email?

There you have all names splitt by forename and surname.

What's the meaning of making another worksheet with the same value.

[AndyLitch]

If I were you I would use Data Validation for your mailshot worksheet using your email database as a selection list. That ensures typo errors can't be made.

[kyjae]

Hi,

Ok, the live scenario is that the person's email and membership status is drawn from an existing separate spreadsheet. So, we have one main spreadsheet for mail shots and a separate primary membership spreadsheet updated and added to by others - hence the potential for errors and typos.

[oeldere]

@Andy Litch

Please Login or Register  to view this content.

I don't know if datavalidation is possible in WORD.