Comparing Two workbooks
I am comparing two separate workbooks to find out if an order that had to be
reshiped was returned. within "2009 Reship Orders" spreadsheet I list the original order number, within my 2009 Returns spreadsheet, I have all the order #s of packages returned. I want to check the original orders from my 2009 Reship Orders spreadsheet against the order #s on my 2009 Returns spreadsheet to see if the package was return, if so put YES in the column, and leave blank if not. This is the formula that I have in cell m310 of my 2009 Reship Orders =IF(INDEX('[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,MATCH(I310,'[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,0))=I310,"YES","") This gives me the yes when it does find a match, however instead of giving me "" back when it is not found, I get #N/A. Can someone tell me what I did wrong? |
Comparing Two workbooks
You need to test if a match is found like this if(ISNA(match(your match formula)),"",Index(your full index formula)) "AJ" wrote: I am comparing two separate workbooks to find out if an order that had to be reshiped was returned. within "2009 Reship Orders" spreadsheet I list the original order number, within my 2009 Returns spreadsheet, I have all the order #s of packages returned. I want to check the original orders from my 2009 Reship Orders spreadsheet against the order #s on my 2009 Returns spreadsheet to see if the package was return, if so put YES in the column, and leave blank if not. This is the formula that I have in cell m310 of my 2009 Reship Orders =IF(INDEX('[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,MATCH(I310,'[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,0))=I310,"YES","") This gives me the yes when it does find a match, however instead of giving me "" back when it is not found, I get #N/A. Can someone tell me what I did wrong? |
Comparing Two workbooks
Thanks for the help, it worked, I get a true message if found which is fine.
Just curious is there a way for it to say yes instead of True? "joel" wrote: You need to test if a match is found like this if(ISNA(match(your match formula)),"",Index(your full index formula)) "AJ" wrote: I am comparing two separate workbooks to find out if an order that had to be reshiped was returned. within "2009 Reship Orders" spreadsheet I list the original order number, within my 2009 Returns spreadsheet, I have all the order #s of packages returned. I want to check the original orders from my 2009 Reship Orders spreadsheet against the order #s on my 2009 Returns spreadsheet to see if the package was return, if so put YES in the column, and leave blank if not. This is the formula that I have in cell m310 of my 2009 Reship Orders =IF(INDEX('[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,MATCH(I310,'[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,0))=I310,"YES","") This gives me the yes when it does find a match, however instead of giving me "" back when it is not found, I get #N/A. Can someone tell me what I did wrong? |
Comparing Two workbooks
That worked thanks, get TRUE in the cell if found. Just curious, is there a
way for it to say Yes instead of True, not that important if not. "joel" wrote: You need to test if a match is found like this if(ISNA(match(your match formula)),"",Index(your full index formula)) "AJ" wrote: I am comparing two separate workbooks to find out if an order that had to be reshiped was returned. within "2009 Reship Orders" spreadsheet I list the original order number, within my 2009 Returns spreadsheet, I have all the order #s of packages returned. I want to check the original orders from my 2009 Reship Orders spreadsheet against the order #s on my 2009 Returns spreadsheet to see if the package was return, if so put YES in the column, and leave blank if not. This is the formula that I have in cell m310 of my 2009 Reship Orders =IF(INDEX('[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,MATCH(I310,'[2009 Returns spreadsheet.xlsx]Mar'!$H$6:$H$146,0))=I310,"YES","") This gives me the yes when it does find a match, however instead of giving me "" back when it is not found, I get #N/A. Can someone tell me what I did wrong? |
All times are GMT +1. The time now is 04:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com