The countries are ranked in order based on the medals the country has won in races. The best country has won more gold medals than other countries. The countries which did not win any gold medals are ranked based on how many silver medals they got. The countries which did not win any gold or silver medals are listed by the number of their bronze medals.

If country won one gold medal is that country ranked higher than a country which won 10 silver medals but no gold medals.

G, S and B stand for gold, silver and bronze, just for clarification.

We need to create a reference number for all the countries, the reference number depends on the number of medals the country achieved.

The number of gold medals is multiplied by 1000 and number of silver medals by 100. Then number of bronze medals is added.

Country A won nine gold medals, six silver medals and eight gold medals. Therefore, the reference number for the country A is 8 * 1000 + 6 * 100 + 8= 9608.

Then we can rank the countries, so that number one is the best country.

In our case, function RANK.EQ holds just two arguments. Number, which is ranked, and reference numbers to which the number is compared. The third argument, order, is also possible. In this case, it is not needed. If we wanted to rank the last country as one, we would just enter 1 as order.

Finally, we could set up a podium. We mark 1, 2, and 3. Then Excel should look for us to decide which country is the first, second or third.

For that, we need two functions INDEX and MATCH. I will introduce the functions.

For INDEX the area B2:E4 is defined first. Row selection is three, meaning we take the third row in the area, meaning row starting with nine. Column selection two means that we take the second value from that row, that is ten.

We have lookup value in B2 and lookup array from B4 to B8. Match type is zero, we are looking for the exact match, sharp two. The number two is the fourth number is the array.  

First, we need to use MATCH function to find out that number one is the third number in I4:I13 array. Then we take the third country from C4:C14.

The sentence in L4 is:

=INDEX($C$4:$C$13; MATCH(K4;$I$4:$I$13;0))

A company of changing chart of account and the system how the book keeping accounts are numbered. Earlier the accounts consisted of four digits. Now all the accounts will have five digits. An extra zero will be added between the first and second digit. For example, 1320 will be 10320 and 1090 will be 10090. How can we automate the change with Excel ?

=LEFT(B4;1)&$B$2&(RIGHT(B4;(LEN(B4)-1)))

First we need to take one digit from the left with LEFT(B4;1). The with &$B$2& we add the zero from B2. I have taken zero as reference, if the second digits should be something else than zero, then update the B2 cell. The rest RIGHT(B4;(LEN(B4)-1))) takes digits from right. We are taking three digits as the length of account is four digits and we subtracted one. I did not want to define the number of characters for RIGHT function with static three, as now the sentence works also with numbers longer or shorter than four digits.

The result is correct, but you noticed that numbers aligned to left. Therefore, the values are not numbers. If you need to calculate anything, the cell values should be numbers. As values look like numbers, it would be better that cell category is number.

Only NUMBERVALUE function is added, and the results are in numeric value. The results are aligned to right.

=NUMBERVALUE(LEFT(B4;1)&$B$2&(RIGHT(B4;(LEN(B4)-1))))

Now we have correct account number. Next task is to find account descriptions from the old account numbers and match them with the new account numbers.

This we can do with VLOOKUP. As we know, VLOOKUP consists of four arguments:

1. Lookup value, which value we are looking for.
2. Table array, where the value is being looked for
3. Column index number, as the value was found in most left column, at which column the value is captured.
4. Range lookup, we are looking for the exact match for lookup value.

The key in our case is that lookup value may be formed with formulas.

The cell E4 holds the value for 10000, that is the new number for 1000 cash account. We need to have the description for the account 1000 to 10000. The cell value of 10000 needs to be converted to lookup value 1000.

NUMBERVALUE(LEFT(E4;1)&RIGHT(B4;LEN(E4)-2))

We need to take one value from left and then from right the length of the cell subtracted by two. The result needs to be converted into number value. That is how we get 1000 from 10000.

The whole VLOOKUP is:

=VLOOKUP(NUMBERVALUE(LEFT(E4;1)&RIGHT(B4;LEN(E4)-2));B4:C9;2;FALSE)

In B and C columns we have old accounts and descriptions. In E columns we have accounts with new numbering, an extra zero has been added between 1^st and 2^nd number. The description from the old accounts is fetched with VLOOKUP to the new account numbers. Now we have the new chart of accounts in use.