nested IF & ISERROR function

Question

I have 2 workbooks & want to compare the contents of column A. If the value in A8 is not matched in the second workbook, I want it to be the result of the formula.

I have this formula which works OK.

=IFERROR(MATCH([Latest_DEBTORS.xls]sheet1!A8,Debtors!A:A,0),[Latest_DEBTORS.xls]sheet1!A8)

However, if there is a match I get the cell number & what I'd really like is a "" result to keep the cell blank as the result I really want is the contents of A8 if it is not matched. I have tried all manner of nested IF formulas but just can't get it right.

Of course, there also may be a far better way to do this.......................

Answer 1

Please download the 2 files from below links where this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Book1.xls

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Latest_DEBTORS.xls

The formula in Book1.xls file is the same as you have mentioned, only it will not have the $ sign before 8 ie. instead of $A$8 it will be $A8 because the formula is being used for multiple rows.

=IF(ISNUMBER(MATCH([Latest_DEBTORS.xls]sheet1!$A8,Debtors!A:A,0)),"",[Latest_DEBTORS.xls]sheet1!$A8)

Regards,

Amit Tandon

If this response answers your question then please mark as Answer.

Answer 2

I didn't notice the extra $ sign! Thank you, this works well.

:)