Results 1 to 2 of 2

Thread: Complicated Lookup with conditions in the lookup range cells

  1. #1

    Complicated Lookup with conditions in the lookup range cells

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


    I have this puzzled that i couldnt completely figure out so hopefully someone can help me out with that (p.s. trying to avoid VBA)!

    What i want to do is to look up a unique reference that combines all the possible variances in search criteria's (e.g. C1,C2,C3,C4,C5,C6) in a range that sums up the conditions of which criterias to follow in the lookup. For example:

    Unique reference: =C1&C2&C3&C4&C5&C6
    Lookup Table:
    Condition Summed C1 C2 C3 C4 C6 C6 Tag
    A1*F1 A1 F1 Tag1
    <>A6 F2 Tag2
    <>A3,<>A4 F3,F4 Tag3
    <>A4 F5 Tag4
    <>A4 F6 Tag5
    F7 Tag6
    F8 Tag7
    A5 F9 Tag8
    A6 F10 Tag9
    A7,A3,A4 <>F11,<>F12,<>F13,<>F14,<>F15 Tag10
    F16 Tag11
    F11,F12,F13,F14,F15 Tag12
    C1 - C6 conditions that needs to be follow that either contains these in part of unique reference string or <> does not contain

    Tag - the Tag which should be looked up and showing as a result

    Condition summed - was what i was trying to use to come to the result. I was using wild card to look up, but the formula below does not work if its with sign that of <> that if the criteria exists, it should be looked up by that tag.

    =LOOKUP(9.999E+307,SEARCH(Conditions_summed_range,Unique Reference),Lookup_Tag_range)

    The reason i need this is that the conditions will always be changing and to change the formula everytime it does is too much inefficient due to the amount of criterias.
    Last edited by JustLukas; 2014-10-15 at 11:51 AM.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    Can you post an actual sample workbook? Include some sample inputs and expected results.

Tags for this Thread

Posting Permissions

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