I have a HDI(Human Development Index) database from my country where it includes every city. In case you don't know, UNPD uses certain thresholds to rank countries between 0 and 1, ranking them like:

Very high human development 0,8 - 1

High human development 0,7 - 0,799

Medium human development 0,5 - 0,699

Low human development 0 - 0,499

The problem comes when I need to color rank these cities

Right now I have something like this:

Position City              IDHM     IDHM-R  IDHM-L  IDHM-E
1 º Cuiabá                 0.785    0.800   0.834   0.726
2 º Sinop                  0.754    0.755   0.832   0.682
... ...                    ....      ...    ..      ...

But I'll need to transfer this database to another software including the code to make each number have a different color according to each rank, where they would look like this:

1 º Cuiabá                 AAA0.785  ZZZ0.800   ZZZ0.834    AAA0.726
2 º Sinop                  AAA0.754  AAA0.755   ZZZ0.832    BBB0.682
... ...                    ....      ...    ..      ...

My question is, does anyone know if there is a quick way to add this info automatically instead of having to input it manually?

I apologize for my poor english and thank you for your attention.

  • Do the AAA0, ZZZ0, BBB0 relate to the categories of development, i.e. ZZZ0 is Very high, AAA0 is High, BBB0 is Medium. If so, what is the code for Low? – teylyn Jan 29 '16 at 5:02

Create a lookup table that looks like this:

number  description code
0       Low         CCC0
0.5     Medium      BBB0
0.7     High        AAA0
0.8     Very High   ZZZ0

Change the code for the "low" value to whatever you want to use. It was not clear from your question.

Now you can use Vlookup with an approximate match to find the code. With a 1 or TRUE as the last parameter, Vlookup returns the value that is equal to or smaller than the lookup value.

Consider the following screenshot:

enter image description here

The yellow shaded cells contain your expected results from the question.

The current cell is H2 an has the formula


copied across and down. In words: In the first column of the table in M1:O5 find the cell that is either equal or smaller than the value in B2. Return the value from the third column. Then append a dot and append the value of B2.

For comparison purposes, the desired results from your question are below the formula table, highlighted in pale yellow.

Note that the three tables can be on different sheets. You just need to adjust the references.

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.