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

Hi Anit, both files are open. I am afraid I cannot upload these files, as you may guess from the filenames they contain confidential information. But I gave you an exmple of the data in Column A of both files that I wish to pull in.

File Debtors_LATEST Cells A8 to A20

A015
A020
A023
A037
A045
A053
A088
A094
A108
A109
A141
A149
A168

File in which formula resides cells A8 to A20

A015
A020
A023
A037
A045
A053
A088
A108
A109
A141
A175A
A193
A214

My formula results (showing what I am looking for in bold).

8
9
10
11
12
13
14
A094
15
16
17
A149
A168

I just need the other cells to be blank.

Answer 3

Please ensure that the Latest_Debtors workbook is open. If the problem persists then please upload both the files.

Answer 4

Thanks for your help Amit. I replaced the sheet names as below

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

Latest_Debtors is the file I want the content of cell A8 from if it doesn't appear on sheet "Debtors" of the current workbook.

Perhaps I should have explained that the cell content I want is an alpha numeric account number.

Both formulas just produce blank cells.

Answer 5

Try any of the below formulas:

=IF(COUNTIF(Sheet1!A:A,Sheet2!A8)>0,"",Sheet2!A8)

or

=IF(ISNUMBER(MATCH(Sheet2!A8,Sheet1!A:A,0)),"",Sheet2!A8)

If cell A8 in Sheet2 has a match in column A of Sheet1, then "" (blank) is returned, else if no match then cell A8 value of Sheet2 itself is returned.

Please replace the sheet names.

Regards,

Amit Tandon