You have a report of actual figures, then you also have two budgets B1 and B2. You would like to compare the two budgets to actual in turns.

In our example, data is very small to characterize the solution.

To find the budget, we will use VLOOKUP function.

The formula in B2 is simple =VLOOKUP(A2;$A$11:$C$16;2;FALSE).

However, as the 490 was not budgeted, there is no budgeted value for 490, but just #N/A. That cell cannot be used in calculation.

This can be tackled with ISERROR function. If VLOOKUP returns an error, the IF returns just an empty cell.

=IF(ISERROR(VLOOKUP(A3;$A$11:$C$16;2;FALSE));””;(VLOOKUP(A3;$A$11:$C$16;2;FALSE)))

We should have a parameter when we change the parameter, then we could switch B1 to B2 or visa versa.

The VLOOKUP consists of four arguments.

1. Lookup value: the number of the unit.
2. Table array: the budget area.
3. Column index number: how many steps we take from column A to right.  A itself is counted as one.
4. Range lookup: is the exact lookup value searched for or an estimate.

The difference between B1 and B2 is that column index number must be for B1 two and for B2 three. As we need to take one more step from B1 to B2.

We also need to have a surrounding IF sentence to define whether we want to have B1 or B2.

E1 is the parameter to select B1 or B2. If neither of B1 or B2 is selected, then the budget column should be empty.

The sentence is here for copy pasting.

=IF($E$1=”B1″;IF(ISERROR(VLOOKUP(A2;$A$11:$C$16;2;FALSE));””;(VLOOKUP(A2;$A$11:$C$16;2;FALSE)));IF($E$1=”B2″;IF(ISERROR(VLOOKUP(A2;$A$11:$C$16;3;FALSE));””;(VLOOKUP(A2;$A$11:$C$16;3;FALSE)));””))

First the sentence checks whether E1 holds B1 or B2. If the value is B1, then just use VLOOKUP and column index number is two. If the result is error, then return an empty cell. If E1 equals to B2, the VLOOKUP has column index number of three and return the third column in table array. If the value in E1 is something else than B1 or B2, then just empty values are returned.