Look up / list help

[EmmaF232]

Name	Manager
Agent 12	WANT THIS TO POPULATE AUTOMATICALLY BASED ON NAME
Agent 45

<TBODY>
</TBODY>

Ok Will try my best to explain this one but you help would be apreciated,

I want people to be able to choose an agent name then the Manager Cell will populate automatically based on the agent
I have a sheet with a list of all 200 agents and 20 Managers and in a list of who is on whos team team etc

For example

Joe Bloggs (Manager)	John Smith (manager)
Agent 4	Agent 22
Agent 67	Agent 134

<TBODY>
</TBODY>

What formular do i need so that the manager is returned when an agent name is entered

Many thanks look forward you responses

Emma

 

[Andrew Poulsom]

Where is the list of managers and their staff (worksheet name and range address) and where is the table that you want to populate (worksheet name and range address)?

 

[EmmaF232]

thanks for the reply i have managed to get what i need now using vlookup

thanks

 

[Teeroy]

I think that the format may be a bit tricky for formulae (still easy to do with VBA if you wanted a macro). If you are able to reformat your data it would be an easy Vlookup. If you set out a single list of Agent | Manager in 2 columns you could use
=Vlookup("agent name",A$1:B$200,2,False)

 

[Andrew Poulsom]

thanks for the reply i have managed to get what i need now using vlookup

How did you manage that with vertical lists?

 

[Hercules1946]

Emma
This array formula may do the trick:

{=IFERROR(INDEX(B$19:B$99,SMALL(IF($B$19:$B$99=$J$18,ROW(B$19:B$99)-18),ROW(B1))),"")}

This allows you to find instances of a search value in a separate table, and extract other detail from the matching entries (Like a database search). Its status as an array formula is indicated by the curly braces around the formula. You can't type these in directly - you have to complete the formula and then press Control+Shift+Enter to get Excel to put them in for you. Also, you need to repeat this each time you edit the formula.
Ive produced an example that gets employee data from a larger table based on a drop down selection in cell J18

Sorry I cant find a way to upload the example.

HTH
Hercules

 

[EmmaF232]

How did you manage that with vertical lists?

Yes used a vertical list and vlook up, the only thing i would ask not is if i am dragging the formular down is tere any way to stop is from returnin #N/A where an agent name has not been selected yet as it is going to be a sheet where people go in and fill in information against the agent

Thanks

 

[mmmarks]

try dis,

=if(agent name ="","",here ur formula)

 

[Hercules1946]

If you add the IFERROR function to Teeroys formula, this will not report the #N/A:

=IFERROR(Vlookup("agent name",A$1:B$200,2,False),"")

Youll need Excel 2007 or later. The older IF(ISERROR( works as well, but you have to put the formula in twice, which is a bit of a pain.