# Solved: Excel if cell contains vlxp then put matching cell data in current cell

Discussion in 'Business Applications' started by mariaa33, Sep 10, 2010.

Not open for further replies.

Joined:
Aug 7, 2008
Messages:
164
In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?

2. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
I think you can replace your formula with this:
=IF(ISERROR(INDEX(K2:AB2,MATCH("*VLXP*",K2:AB2,0))),"n/a",INDEX(K2:AB2,MATCH("*VLXP*",K2:AB2,0)))

3. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Let's make that easier to read:
Code:
`=IF(ISERROR(INDEX(K2:AB2,MATCH("*VLXP*",K2:AB2,0))),"n/a",INDEX(K2:AB2,MATCH("*VLXP*",K2:AB2,0)))`

Joined:
Aug 7, 2008
Messages:
164
Slurpee55, you are awesome! I have tried so many different combos of match and index with no luck. Thank you.

5. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Your formula was pretty interesting because of some of the tricks you pulled on Excel - actually more complex than the Index/Match!

As Seen On