I would like to use INDEX/MATCH to look up values based on the combination of characters in a cell, but LEFT/RIGHT will not work because the length/order of characters is not consistent. Specifically:
I have a set of scores for a competition which consist of up to 3 characters:
A number (1-5), a modifier (+/-), and a category (A/B)
For example:
5+A
3-B
2-A
The number is always present, but the modifier and/or category are often omitted..
5A (no + or - in between)
2+ (no category A/B)
3 (no modifier or category)
Note that there is NOT a blank space in place of omitted characters. A score of 5 is simply 5 (not 5[space][space]). (I tried using a placeholder for omitted characters but this resulted in a LOT of data entry errors when entering many scores rapidly).
I would like to use INDEX/MATCH to look up values based on these scores in a table similar to the one below. Since the modifier and/or category may be omitted in any combination, I can't do a simple LEFT / RIGHT.
Any suggestions for how to make this happen? I'm imagine there's an obvious solution right under my nose and I'm just overthinking...
Thank you.
Bookmarks