1

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.

2

Use this instead:

=VLOOKUP(E3,'Flat Scoring'!J17:K26,2,FALSE)

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).

1
  • @Blue7, BTW, 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). – Erik Tyler Jan 1 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.