Lookup data on different worksheetsSolved/Closed

Question

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.

rizvisa1 · Answer

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.

DCP · Answer

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,"")))

Lookup data on different worksheets

2 responses

Similar discussions

Subscribe To Our Newsletter!