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