IF, AND, VLOOKUP jumble

[Lee Grant]

Hi there,

Just when I think I'm getting used to Excel, I come up against something
that reminds me how much I don't know. I hope one of you good guys can help
me out.

Essentially I have two worksheet listing competitors in an annual race. One
worksheet lists 2008 (let's call it 2008) entrants:

B C
1 Fred Blogs
2 John Perry
3 Martha Doe

The other worksheet lists 2007 (let's call it 2007) entrants with their time
from last years race:

C D H
1 Fred Blogs 01:09:44
2 John Perry 01:29:14
3 Dave Sherwood 00:39:52
4 Shelly Gone 00:44:32

What I want to try and achieve is to have cell O3 on the 2008 worksheet for
it to report either "yes" or "no" depending if the entrant raced last year.
If the answer is a "yes", last year's time should appear in P3.

I've tried various IFs, nested IFs and lookups and I just cannot seem to get
it where I want it.

The 2008 worksheet is a 'work in progress' as entrants are still coming in
so new rows are being added all the time.

I hope I've explained this coherently..

Any help would (as always) be greatly appreciated.

 

[Dave Peterson]

How about True/False?

In D1 of the 2008 sheet:

=isnumber(match(1,(b1='sheet1'!$c$1:$c$999)*(c1='sheet1'!$d$1:$d$999),0))
(and drag down)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

 

[Lee Grant]

Thanks Dave.

That did the trick. I can use a vlookup for the time section.