Your formula actually tests the condition twice. It only needs to be:

=IF(ISNA(MATCH(B158,'formula Test'!$A:$A,0)),"No Match Found",B158)

What you want now, though, is this:

=IF(ISNA(MATCH(B158,'formula Test'!$A:$A,0)),"No Match Found",IF(INDEX
('formula Test'!$D:$D,MATCH(B158,'formula Test'!$A:$A,0))="","",INDEX
('formula Test'!$D:$D,MATCH(B158,'formula Test'!$A:$A,0))))

This will return No Match Found if there is no match and will return
an empty cell if the corresponding cell in column D is empty when a
match is found.

Hope this helps.

Pete

On Nov 17, 10:43*am, gootroots
wrote:
Hi,

The following formula establishes if there is a match for B158

=IF(ISNA(MATCH(B158,'formula Test'!$A:$A,0)),IF(ISNA(MATCH(B158,'formula
Test'!A:A,0)),"No Match Found",B158),B158)

I now want to modify and add an extra condition.

When a match is found in 'formula Test'!$A:$A *I want to establish if the
corresponding cell *in column D contains a value and return that value
instead.

For example:

B158 = “Apples”

“Apples” has been match to A364 in 'formula Test'!$A:$A

The cell in D364 'formula Test'! *= “Red Apples”

The formula should return “Red Apples”

If cell D364 had not contained a value then nothing should be returned.