I want to have a simple star rating system that a user can select the number of stars, and then this information will be used to calculate a total score.
I have a list of stars, and an associated value with each:
| Location Rating (I16) | Symbol (J16) | Weighted (K16) |
|---|---|---|
| 5 | ★★★★★ | 5 |
| 4.5 | ★★★★1/2 | 4.5 |
| 4 | ★★★★ | 4 |
| 3.5 | ★★★1/2 | 3.5 |
| 3 | ★★★ | 3 |
| 2.5 | ★★1/2 | 2.5 |
| 2 | ★★ | 2 |
| 1.5 | ★1/2 | 1.5 |
| 1 | ★ | 1 |
| 0.5 | 1/2 | 0.5 |
I then have a cell E3 where the user enters the location rating. It has data validation that it must be an item from list in: 'Flat Scoring'!J17:J26.
The score for this location is then used using a lookup table:
=LOOKUP(E3, 'Flat Scoring'!J17:J26,'Flat Scoring'!K17:K26)
However, if for example I select 3 stars in E3, I get the error "Did not find value ★★★ in LOOKUP evaluation."
I think this is because the LOOKUP function does not work with Unicode characters.
Is there a workaround for this bug, or another way to achieve what I want?
edit: My assumption that the bug is due to Unicode may be wrong. Even if I select the "1/2" it still fails.
Best Answer
Use this instead:
The reason your LOOKUP failed isn't that LOOKUP doesn't work with Unicode. It's that LOOKUP only works on ranges with a clear sort order. Since you are mixing stars, numbers and the slash, you are placing code values in a non-sorted order (i.e., a star is code 9733, a slash is 47, and 1 and 2 are 49 and 50 respectively, which you have mixed).
VLOOKUP, on the other hand, allows us to assign with the last argument whether the search range is sorted or not (in your case, that is FALSE).