Finding text in an array but not always in same place and returning adjacent cell value

Hi there, I'm trying to automate a process that I've always done manually in the past.

I look after the weekly scores from a quiz league and receive a worksheet with the names of the teams and their scores.

The problem I have is that not all teams compete each week so their names and scores are not always there.

How can I get excel to look for a team name in a column and return the score of the adjacent cell into a different worksheet and next to the correct team name.

I thought I had it using simple IF functions but no progress there.

Can anyone suggest a way of doing this please.

I've got up to 600 team names now so it's a nightmare doing it by hand.

Thanks in advance.

ps this is the first time I've ever posted anything on an online forum so I hope I haven't broken any rules.

TB

Hi TMS, many thanks for that. I've taken a look at VLOOKUP and applied it.
Works well.
If used it in the following formula...
=IF(ISBLANK(A2),"",VLOOKUP(A4,wk1scores,2,FALSE))

Works perfectly but returns #N/A for teams that haven't played ie the teams that don't appear on the list on any given week.

Would you know how to refine the formula to return a '0' instead of the #N/A?

This would make summing the scores easier without manually changing each cell.

Thanks,

TB

Hi TMS,

Thanks Again.

The A4 ref is in error, both should read A2.

That's nice and tidy now.

Would you recommend some reading for me here.

I'm self taught on the areas I've needed for work and hence many gaps.

Cheers,

TB