# Using LOOKUP across several sheets to autofill and count

1. ## Using LOOKUP across several sheets to autofill and count

Hi everyone

I've tried to complete this task with my limited knowledge and I've hit a brick wall.

The attached document contains three sheets. Here's what I'm trying (and failing) to do:

When entering a postcode (zip code for those outside the UK) in to Column C of the 'Participants' sheet, I want it to search the sheet titled 'Combined postcodes', Column A, and autofill in to column E in the 'Participants' sheet the ward as listed in Column C the 'Combined postcodes' sheet.

Once this is done, I want to be able to count how often the Ward appears in the sheet 'Participants' and show it against the ward name in the sheet 'Ward totals'.

To complicate this a little further, if a postcode entered in the 'Participants' sheet does not correspond with any postcodes in 'Combined postcodes' sheet, I want it to autofill as 'Outside area' and this would be counted against the Outside area tally on the 'Ward totals' sheet.

I realise that this seems like a big task (well it does to me) but I'm hoping that for some of you very experienced Excel users this will be a walk in the park!

I will really appreciate your help on this. I hope someone can do me a huge favour!

Ian

2. ## Re: Using LOOKUP across several sheets to autofill and count

Participants E2
=IFERROR(VLOOKUP(C2,'Combined postcodes'!\$A\$1:\$C\$10592,3,),"Outside area")

Ward totals B1
=COUNTIF(Participants!\$E:\$E,A7)

3. ## Re: Using LOOKUP across several sheets to autofill and count

Hi Bo_Ry

Thanks for the help. I really do appreciate it.

The first one worked like a charm. Thank you!

The second one needed a tweak as I don't think I quite explained it properly. I need to enter it for each specific ward like this - =COUNTIF(Participants!\$E:\$E,"Southwick") so it counts the number of times the ward 'Southwick' is mentioned. But I couldn't have fathomed that without your initial suggestion. So thank you very much.

There are currently 1 users browsing this thread. (0 members and 1 guests)