I'm struggling to write down the correct function to perform a calculation in excel. In my first sheet, I have a list of towns in Albania, with a corresponding letter "U" or "R" if they are located either in an urban or rural environment. In sheet 2, I have the corresponding literacy rate of either rural or urban environments for a set of countries, including Albania. Therefore, in Sheet 1, under "Literacy Rate %" I'd like to attribute for each Albanian town an urban or rural literacy rate using the values of sheet 2. I was combining if function and vlookup but I'm failing somewhere... My data is illustrated in this picture : enter image description here

  • what have you tried so far? And why use a VLOOKUP if you ony use the data from Albania and no other country? – cybernetic.nomad May 6 '19 at 13:06
  • @cybernetic.nomad I've found out how to do it. I did: =IF("townA="R";VLOOKUP(ISOCODE;Sheet2"Literacy rate for rural";)VLOOKUP(ISOCODE;"literacy rate for urban")) – user156 May 7 '19 at 10:31
  • And it works alright. I used VLOOKUP as I am focusing on other countries as well, Albania was just to illustrate the example. Thanks for your input – user156 May 7 '19 at 10:32
=IFERROR(VLOOKUP($A3,'Sheet2'!$A$2:$D$7,IF(I3="R",4,IF(I3="U",3,0)),0),"MISSING INFO")

Place the above formula in J3 of sheet1 and copy down as required.

The above formula can be broken in to 3 separate formulas. Let start with VLOOKUP. Its going to look up you code from cell A3 in the table on sheet2 and its looking for an exact match based on the last zero in the formula In order to determine which column to return , either 3 or 4, an IF function is used to determine what is I3. A single IF could have being used if it is assumed that when I3 does not equal R the only other possibility is U. In the equation above, I used a nested IF function to cover a third possibility of a blank where the U or R was not filled in. In hindsight it also covers the possibility of data entry error where a letter other than R or U was entered. In the third case 0 is returned in order to produce an error.

The whole thing is placed in an IFERROR function. When the VLOOKUP produces an error it could be because the U or R was not properly set, or that the country code was not found in the table. Either way you a missing proper information. I choose that the error message of MISSING INFO be displayed in the cell.

Edit the cell ranges, formulas, and message to suit your needs.

  • Thanks for your input, I've found my way around another formula, explained above. It works just fine. – user156 May 7 '19 at 10:33

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.