Modify and add extra condition

G

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

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
 
M

Ms-Exl-Learner

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top