locked
Does a vlookup contain a specific value RRS feed

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

    Can anyone help please?

    Regards,

    Pete

    Tuesday, June 11, 2013 2:00 PM

Answers

  • =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 ryguy72 Thursday, June 20, 2013 1:50 AM
    • Marked as answer by Damon Zheng 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 ryguy72 Thursday, June 20, 2013 1:50 AM
    • Marked as answer by Damon Zheng Sunday, June 23, 2013 3:36 PM
    Tuesday, June 11, 2013 2:29 PM
  • Thank you sir.
    Tuesday, June 11, 2013 2:35 PM