Google-sheets – Show image if rank equals number

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I have a row of 5 cells. I want to show an image above each cell depending on the rank of the cell. So if the rank of the cell equals 1, show a specific image (from a link), if the rank is 2, show a different image, and so on.

Here is what my sheet looks like now:

+-------+------+------+------+------+------+
| Image |      |      |      |      |      |
+-------+------+------+------+------+------+
| Score |  100 |  300 |  200 |  500 |  400 |
+-------+------+------+------+------+------+

How would I go about achieving this?

Best Answer

=image(vlookup(rank(B2,$B$2:$F$2),images!$A$1:$B$6,2,false))

Enter this in cell B1 and then copy across as many columns as necessary.

This formula combines three nested functions:

  • rank - the dataset is an absolute reference; this enables the formula to be copied and retain the reference to the dataset.
  • vlookup() - takes the rank value and returns the image url from the range. Again, the range is an absolute reference so that it will apply to any rank value.
  • image() - displays the image url returned by vlookup

Assumptions:

List of Image urls (one for each rank value)

Range A1:B6 of sheet="images"

Rank   |    Image URL  
5      | https://www.google.com/images/srpr/logo3w.png  
4      | https://clipartart.com/images/book-jpg-clipart-2.jpg  
3      | https://clipartart.com/images/book-jpg-clipart-4.jpg  
2      | https://clipartart.com/images/book-jpg-clipart-9.jpg  
1      | https://clipartart.com/images/book-jpg-clipart-3.jpg

Scores:

List of scores

Range B1:B6

Score| 100 | 300 | 200 | 500 | 400
Related Topic