AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 657
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
Hi there,
I need a lookup formula that compares a given value against a column in a table (with text of variable length) and returns a match if the lookup value begins with the whole of the matched string.
So for example, in this example, I need to match "DEFLPQS" with "DEF" in the lookup column because "DEFLPQS" starts with "DEF", and then return the corresponding value 20. If the value was "DE" it should not return a result because it has to start with the whole of the string in the lookup column. And the strings in the lookup column could be of any length.
Also - if possible - I would really like for the formula to return a best match if more than one can be found ("best" meaning the longest string in the lookup column that satisfies the criteria)
So for example :
Here, the lookup value "DEFLPQS" matches against three different rows in the lookup column ("DEF", "DEFL" and "DEFLP") but I want to return the associated value for "DEFLP" (500) because that is the longest matching string in the lookup column (5 characters as opposed to 3 or 4) that meets the criteria.
I am used to using INDEX/MATCH for this kind of thing but only if the wildcard is appended to the value being looked up, not to all the values in the lookup column? And/or a LEFT() function.
Any suggestions?
Thanks!
AOB
I need a lookup formula that compares a given value against a column in a table (with text of variable length) and returns a match if the lookup value begins with the whole of the matched string.
So for example, in this example, I need to match "DEFLPQS" with "DEF" in the lookup column because "DEFLPQS" starts with "DEF", and then return the corresponding value 20. If the value was "DE" it should not return a result because it has to start with the whole of the string in the lookup column. And the strings in the lookup column could be of any length.
Value | Formula | Lookup Column | Return Value |
---|---|---|---|
DEFLPQS | ABCD | 10 | |
DEF | 20 | ||
GHIJK | 30 | ||
WXYZ | 40 |
Also - if possible - I would really like for the formula to return a best match if more than one can be found ("best" meaning the longest string in the lookup column that satisfies the criteria)
So for example :
Value | Formula | Lookup Column | Return Value |
---|---|---|---|
DEFLPQS | ABCD | 100 | |
DEF | 200 | ||
DEFA | 300 | ||
DEFL | 400 | ||
DEFLP | 500 |
Here, the lookup value "DEFLPQS" matches against three different rows in the lookup column ("DEF", "DEFL" and "DEFLP") but I want to return the associated value for "DEFLP" (500) because that is the longest matching string in the lookup column (5 characters as opposed to 3 or 4) that meets the criteria.
I am used to using INDEX/MATCH for this kind of thing but only if the wildcard is appended to the value being looked up, not to all the values in the lookup column? And/or a LEFT() function.
Any suggestions?
Thanks!
AOB