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.

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.

Try this€¦

=IF(B158="","",IF(ISNA(VLOOKUP(B158,'formula Test'!A:D,4,0)),"No Match
Found",VLOOKUP(B158,'formula Test'!A:D,4,0)))

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"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.