Im not sure if this can be done in a single cell formula, or if I'd
have to delve into the VB side of Excel.

I am wrting a spreadsheet to use in a league, where ti maintains
schedules, calculates points system, rankings, etc. I am trying to
make the scheduling easier, and this is where I am running into a
problem.

I would like to take the value of a given cell, in this case L8, and
use it in a vertical lookup function, where the function would output
back into cell L8.

I have all of teams numbered sequencially (01, 02, 03, 04 - 60). In
Cell L8, I would like to be able to type 3 characters. the first
would be either an H or an A. H being home, A being away. If
right(L8,1)=H then cell color=red. If right(L8,1)=A then cell
color=blue. The second 2 characters will be a team number. Such that
if I type in A05, the cell color will turn Blue, and the name of Team
5 will appear in said cell.

I would very much like tobe able to do this with 1 cell, no hidden
columns or anythng like that. Im not sure if a circular reference
like this can work without the VB code side of Excel.

Please advise

-Dave