Autofill adjacent cell from pre-defined list

Hello,

Relative newbie trying to bumble through making lists.

Is there any way to set a formula or macro so that if a name is typed into a column (say column A), a corresponding piece of data will appear or autofill in an adjacent column (say column F) provided the data is recognised from a pre-set list.

For example Column A (A1 - A1000000) is a list of full names of footballers (Forename and Surname) and Column F is a list of the team they play for.

Every time the same name is typed into a cell in column A I would like the team name to appear in column F. For example if cell A9 contains "Ryan Giggs", cell F9 will automatically fill with "Manchester United" as long as this link has been defined in the list, if cell A10 contains "Didier Drogba" cell F10 will fill with "Chelsea". If the name in column A is not recognised, the corresponding cell in column F would return "default" or "unknown".

My very cursory fiddling suggests there should be a way to do it with the IF formula, but I can't see how you could include a longer list of potential names and matches in the IF formula.

Apologies if this, my first post, is not in the correct format for the forum or in the right place.

I'd be so grateful if anyone could give me a hand with this.

Cheers.

Thanks for your reply,

I sort of see what you mean with VLOOKUP using two columns (H:I in your example) as the preset list, presumably where column H=full name and column I=team but I'm still uncertain as to how to define the relationship between each column in the formula. Can you explain a little more.

I've only just recently worked out VLOOKUP and only at it's most basic. I suppose I figured VLOOKUP was only good for one name, so you could set it to recognise 1 name and repeat the pattern down a column when you autofill if that name is TRUE/FALSE, but I don't know how to get it to recognise multiple names in the same column.

Thanks DILIPandey,

I will do a little more research into it along with the advice of DGagnon.

Many Thanks

Thanks DGagnon,

That's really helpful. I'll have a go at it this evening and maybe post back if I can get my head around it.

Best

DGagnon's answer worked perfectly for this problem. I've got it all working now.

Many Thanks for the super swift replies.