The question i have is probably simple but I can't seem to find it.



I started an excel spreadsheet of a list of college football players and the list is rather long to the point where it is taking me some time to search for names that i have already listed. So i wanted to start a function that allowed me to find a players last name within a spreadsheet. There are a list of Last Names that cover more than one column so I simplified it by naming a range of Columns that have these last names and calling it "Last_Names".

The issue that i am having is i want to a cell (for example cell A2) to have the function and the cell to the right of it (cell A3) to be the value im looking for.

Here is the issue: I want to be able to type in a players last name and have the function take me directly to the cell that contains that Name. I DONT want the function to return a value telling me there is 1 or 2 results. I DONT want that function to tell me there is a match and thats it.

Ideally i want to be able to type in a name and have excel take me to that name in the spreadsheet. If i could get that cell to highlight making it easier to spot with my eye that would be great.

Thoughts?

You can use conditional formatting to highlight cells that contain the same information as what you typed in a cell.

Select the range that contains the list of last names, then go to Home|Conditional Formatting|New Rule.

Select "format only cells that contain" from top and enter cell value > is equal to > and then =$A$2

Click Format and choose from the Fill tab.

Click Ok, then Ok again to apply.

Now type a name in A2 and see if it hightlights the name in the list of names.

And to think i was wasting my time in the Formula section researching lookup functions.


This works flawlessly. Exactly what i was looking for and it works.


Thank you!

Then filter the column by color (from the conditional format).

After you did find all your players currently, create a separate sheet, p.e. Players.

In this sheet, fill a table with columns p.e. ForeName, LastName, and maybe FullName too;
Enter names of all players into table;
Define this table as Table (Insert>Table, Check 'My table has headers'). Give a meaningful name to defined table, p.e. tPlayers;
Into FullName column of tPlayers, enter the formulaCreate a named range, p.e. lPlayers (Formulas>Name Manager, New), Into RefersTo enter the formula or ;
On sheet with your current table, select all cells where names must be entered. From menu, select Data>Data Validation. Select 'From List', and enter the formula .

Now when you select a cell for players in your table, the cell works like a combo box - you can select a player from table tPlayer. And you can't enter any player name which was not registered before in table tPlayer (and typos are restricted too).

The order the players are listed for selection is determined with order, players are sorted with in table tPlayers.

And you don't need to search the whole entry table anymore, to find out is the player registered before or not.