Not necessarily what you're looking for since this is a non VBA
method, but I have uploaded a file in the Code samples folder of the
Files section of this group, called
'3 games  immediately preceding.xls'
which gives the WIN/LOSS for the previous three games played by the
team in column A, in three columns, on every row.
Ignore #NUM errors, formulae can be filled down/up, but not left/right.
No autofiltering is required. Not very elegant, but can be made so I'm
sure.

I am looking at the worksheet  function you provided. My problem is
that you are so far ahead of me that I am not able to translate your
formula into something that I understand and the techniques of which I
can use in my efforts down the road . . .

Since I have spent several days trying to solve the problem with VBA,
I really want to use that method rather than a worksheet function
which goes haywire when I change the layout (which happens
frequently). Also, to be honest, I have become somewhat obsessed with
the notion that there MUST be a way to use Autofilter in VBA to
accomplish my goal . . .am I mistaken?

If Autofilter absolutely cannot be used as I desire, I have already
written a VBA macro which accomplishes my goal in a very amateurish
fashion, moving up the "team" column one record  at a time until it
finds a "match", then recording the WIN/LOSS in a variable and moving
on up the line.

Thanks for the formula you provided, though--when I get some free time
I need to learn how to use some of these worksheet functions, and your
formula will be a great practice model!

I've added a macro to the file in the files area. It's called by
clicking Button 3 on the sheet after the active cell  is placed
anywhere on the row required. The macro is documented by comments in
the code.
Autofilter in VBA is a dog. The way you suggest is probably the best
and the macro I've written does pretty much what you have already done.

Unfortunately I cannot view any of the files
at the present time--getting the well-loved "Firefox can't establish a
connection to the server at f1.grp.yahoofs.com." error message. I
assume that the problem will be fixed shortly; and then I will feast
my eyes upon how an expert handled it!

You really answered my question by letting me know that Autofilter
just doesn't lend itself well to VBA use. Seems a shame, but if
that's the way it is then I won't waste any more effort on it.

One last question on it, though . . .I notice when I go into the
autofilter mode, the row numbers (column to the left of "A") have
changed color and look inviting. Is there any way to use these
line/row numbers in a macro as a means of identifying the record  that
I want to reference?

Now I am getting the "Document Not Found" message when I try to view
your code  in the files. No problem getting the other ones, just the
one you put in there. Did you remove it?

No I haven't removed it, maybe there's a problem with it having the
same name as the old one on your machine? Perhaps your cache or your
ISP's cache is still expecting the old one's attributes? Anyway, I've
changed its name by adding " b" to the end. Have another go.

Got it now! I'm going to have to take awhile to figure out all the
devices you used--It's amazing that your version takes so little code
and mine takes about 24 inches of code  .