Hello!
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:
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.
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 |
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:
