Spreadsheet Comparison, nested If statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This works
=IF(B4='[CSNA TEST DATA.xls]Sheet1'!$B$2,IF(F4='[CSNA TEST DATA.xls]Sheet1'!$E$2,"yes","check"))

This doesn’t work
=IF(B2='[CSNA TEST DATA.xls]Sheet1'!$B$2:$B$55,IF(F2='[CSNA TEST DATA.xls]Sheet1'!$E$2:$E$55,"yes","check"))

The difference is that I have changed from looking at specific cells to looking at ranges of cells.

What I am trying to do is compare my spreadsheet with a customer’s spreadsheet, both 3,700 lines long, and if:
1. The serial number data in a given cell in my spreadsheet matches the serial number data in a range of the customer spreadsheet go to step 2.
2. After matching the serial number data exactly in both spreadsheets, look at the warranty start date in both spreadsheets and go to step3.
3. If the warranty start dates match exactly in both spreadsheets, insert the word “yes†and if the dates do not match exactly insert the word “checkâ€

Why has my formula gone awry when working with ranges? Or is there an easier way to compare spreadsheets?

Thank you,
 
Hi
if both workbooks are OPEN try:
=IF(COUNTIF('[CSNA TEST
DATA.xls]Sheet1'!$B$2:$B$55,B2),IF(COUNTIF('[CSNA TEST
DATA.xls]Sheet1'!$E$2:$E$55,F2),"yes","check"))

But I think you probably meant (as the above would NOT look that the
data is in the same row):
=IF(SUMPRODUCT(--('[CSNA TEST DATA.xls]Sheet1'!$B$2:$B$55=B2),--('[CSNA
TEST DATA.xls]Sheet1'!$E$2:$E$55=F2)),"yes","no")
 
Back
Top