I need a look up formula (in Col. Q) that matches an “Account Name” in column AE with the corresponding “Account Name” in column N only if the “Type” in column Y matches a given value (in this case “Illustration”) & only return the associated # from column O for the first match. It will always start in row 4.
Any additional matches return “--“, so I can use a sort to drop them to bottom.
Also, any entries that do not match return an “Error”.
The table below would be before a sort which brings to the top only those entries that match with a given #.
Right now I use a simple vlookup to find the matches & resort the table but get all the entries that returns the #, & have too many duplicate entries to deal with.
Any additional matches return “--“, so I can use a sort to drop them to bottom.
Also, any entries that do not match return an “Error”.
The table below would be before a sort which brings to the top only those entries that match with a given #.
Col. N | Col. O | Col. Q | Col. Y | Col. AE | ||
Row 4 | Smith, Bob | 1 | 1 | Illustration | Smith, Bob | |
Row 5 | Smith, Janet | 2 | -- | Illustration | Smith, Bob | |
Row 6 | Jones, Mary | 3 | 3 | Illustration | Jones, Mary | |
Row 7 | Stewart, Tony | 4 | Error | Meeting | Jones, Mary | |
Row 8 | Petty, Richard | 5 | -- | Illustration | Jones, Mary |
Right now I use a simple vlookup to find the matches & resort the table but get all the entries that returns the #, & have too many duplicate entries to deal with.