Question about Microsoft Excel for PC
Any ideas & help appreciated
Excel spreadsheet with around 100,000+ names, titles, numbers, Title & Notes across multiple sheets
I need to a formula search the Title & Notes section - which is a single cell entry against a series of defined lists to separate out things like Title, Seniority, Products etc These lists are in named ranges and the information to be searched is in this single cell per entry. I need to be able return multiple exact matches from each of the search lists.
eg. Title & Info contains information like this:
MD1 '06 - FI Sales - UK IMG's - Ex Barclays 26/1/04 - BB1008
I have defined a list series of selectors for Seniority: shortened below
so in the above example I only want to return "MD" as an exact match from the list under the column for Seniority. I appreciate that this will not be perfect as the search will also pick up those letters in regular words but this is miniscule in comparison to database size.
Under JobFunction - I want to return "Sales" as a match from the selector list of JobFunctions
For information with regards to Client Type & Client Location the formula has to first pick up a "sales" entry. So will need an IF statement.
Client type will be "IMG's" matched from the ClientType list.....
The results should look something like this
Seniority JobFunction Products Client Type Client Location
MD Sales FI IMG's UK
There are people that will have multiple entries for
This is being done to "pre-skill" people prior to input into a relational database.
In effect the formula is like this
MATCH(JobFunction,within cell G2,return matched item(s))
if "Sales" is present then search to add client type and/or location
Clear as mud!!
The best solution may be to re-define the named columns, or to add new names to multi-column arrays, then use the VLOOKUP function.
Note that the "indexed" named cells must be sorted by the index column.
Test (this is a 2 year old question)... Please ignore ths following. The plus symbol does not display.
Upper key plus symbol displays:
Right plus symbol on number pad displays:
[Shift] Right plus symbol on number pad displays:
[Alt]   displays:
slash/slash/plus displays: //
slash/plus/slash displays: / /
Plus Plus Plus displays:
upper Plus Plus Plus displays:
Shift Right plus plus plus displays:
end of plus test.. thanks.
Posted on Oct 06, 2010
Tips for a great answer:
Sep 03, 2014 | Microsoft Excel for PC
May 15, 2014 | Microsoft Corporation Microsoft EXCEL 97...
Jun 04, 2011 | Microsoft Excel for PC
Feb 01, 2011 | Microsoft Excel for PC
Nov 13, 2008 | Business & Productivity Software
Jul 08, 2008 | Microsoft Business & Productivity Software
Jan 07, 2008 | Business & Productivity Software
Oct 10, 2007 | Microsoft Office Standard for PC
Aug 27, 2007 | Microsoft Office Standard for PC
Jan 28, 2016 | Microsoft Excel for PC
499 people viewed this question
Usually answered in minutes!
Level 3 Expert
Level 3 Expert
Level 3 Expert
Step 2: Please assign your manual to a product: