# Does a vlookup contain a specific value

• ### Question

• Morning all,

I thought I would be able to work this out, but it looks like I'm going to have to admit defeat and and seek help.

I have a simple vlookup formula.

=VLOOKUP(G15, 'Open Orders'!B:D,3,FALSE)

However, there are multiple values that could be returned by my vlookup.

All I want to do is to see if any of the possible returnable values is the word 'Ingredient', if it is, then write the word 'Ingredient', otherwise, just put whatever value the vlookup returns.

Regards,

Pete

Tuesday, June 11, 2013 2:00 PM

• =IF(SUMPRODUCT(( 'Open Orders'!\$B\$2:\$B\$1000 = G15)*( 'Open Orders'!\$D\$2:\$D\$1000 = "Ingredient"))>0,"Ingredient",VLOOKUP(G15, 'Open Orders'!B:D,3,FALSE))
• Proposed as answer by Thursday, June 20, 2013 1:50 AM
• Marked as answer by Sunday, June 23, 2013 3:36 PM
Tuesday, June 11, 2013 2:29 PM

### All replies

• =IF(SUMPRODUCT(( 'Open Orders'!\$B\$2:\$B\$1000 = G15)*( 'Open Orders'!\$D\$2:\$D\$1000 = "Ingredient"))>0,"Ingredient",VLOOKUP(G15, 'Open Orders'!B:D,3,FALSE))
• Proposed as answer by Thursday, June 20, 2013 1:50 AM
• Marked as answer by Sunday, June 23, 2013 3:36 PM
Tuesday, June 11, 2013 2:29 PM
• Thank you sir.
Tuesday, June 11, 2013 2:35 PM