Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Lookup data on different worksheets [Solved/Closed]

Report
-
 DCP -
I have 2 spreadsheets with the same town names in column A and different dates in column N.

I have a third spreadsheet with the town name and have to colour the cell red if it appears on spreadsheet 1, blue if it appears on spreadsheet 2 and green if it appears on both spreadsheets.

I then have to have the previous 8 columns coloured the same colour (ie red, blue or green).

eg

Town 1 Mar 8 Mar 15 Mar 22 Mar
Edinburgh red red red red
Glasgow green blue blue

I have been able to use an if statement using vlookup to get the cells red or blue but my sticking point is getting the formula to show if the town appears on both worksheets.

2 replies

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Answer 2 / 2

I have found the formula I was looking for:

=IF(AND(VLOOKUP($D4,Sheet1!$A$3:$O$304,14,0)=E$3,VLOOKUP($D4,'Sheet2'!$A$3:$O$304,14,0)=E$3),3,IF(VLOOKUP($D4,Sheet1!$A$3:$O$304,14,0)=E$3,1,IF(VLOOKUP($D4,'Sheet2!$A$3:$O$304,14,0)=E$3,2,"")))
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!