Previous Topic Next Topic

Lookup data on different worksheets

Solved/Closed
DCP - Apr 27, 2010 at 08:44 AM
 DCP - Apr 28, 2010 at 08:26 AM
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
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 27, 2010 at 03:28 PM
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.
0
Answer 2 / 2
DCP
Apr 28, 2010 at 08:26 AM
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,"")))
0