Modify and add extra condition

[gootroots]

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.

 

[Pete_UK]

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

 

[Ms-Exl-Learner]

Try this…

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

If this post helps, Click Yes!