Results 1 to 2 of 2

Thread: Partial Lookup Text with variable lookup values

  1. #1
    Neophyte melnemac32's Avatar
    Join Date
    May 2021
    Excel Version

    Partial Lookup Text with variable lookup values

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am trying to create a forumla that will tell me on my customer order tab that quality wants to review the order based on the product ID and customer. The customer name can vary (Usually by a customer having various addresses). So I want my spreadsheet to return a yes value if the cell contains the customer name and the product ID. I want to create a blank tab that our quality team can populate data into that will pull in the customer order tab. So my formula below works just fine for the cells that I have information entered into, but I want to have blank lines that quality can input data into. If I change the formula below to C$2:$C$500 and A$2:$A$500 then everything returns back a yes. Any idea on how I can modify this so that data can be entered in?

    =IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$C$2:$C$5,'Customer Orders'!F2)))>0,SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$A$2:$A$5,'Customer Orders'!A2)))>0),"Yes","No")
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Excel Version
    =IF(SUMPRODUCT(ISNUMBER(SEARCH('Quality List'!$C$2:$C$500,F2))*(A2='Quality List'!$A$2:$A$500)*(LEN('Quality List'!$C$2:$C$500)>0))>0,"Yes","No")
    which adds a test for zero-length column C in Quality List sheet.
    Be aware that doing it this way will find Customer 3 in Customer 33 INC , SAINT PAUL , MN
    Last edited by p45cal; 2021-05-05 at 06:55 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts