Seek, and you will find it

"There is nothing like looking, if you want to find something. You certainly usually find something, if you look, but it is not always quite the something you were after."

R.R.Tolkien, Hobbit

Last week we finished our experiments on data validation step. We have used for that purpose column of a supporting table. In this and next posts I would like to explain some methods, you can use to search tables and merge data between.

.
So let's start with file, we created last time. If you don't have saved workbook from last session - you can download a copy here: Tables-03-en.xlsx
When last time I mentioned tax rates list, we have simplified a table, to just basic list of percentage values. What will happen, if you would like to use some special tax codes, like tax exempt? Try to add such tax codes to our list.

• Go to sheet [Dimensions] and add below table new items:

  Exempt
  No tax

  
• Now you will be able to select new tax codes, when you get back to invoices table on first sheet. Unfortunately you will encounter different issue - with formula in calculated column [Gross amount]. Selecting any tax code, which is not a percent/number, will cause error in calculations:
  

So where are the problem origins? Do you recall, how the Gross formula looked like? In the end of my first post, it was written as:

=[Net amount]+[Net amount]*[Tax%]

If we substitute referenced values in formula, we will receive this:

[Net amount] = 250
[Tax%] = "Exempt"
[Gross amount] = 250 + 250 * "Exempt"

Well, is it not possible to multiply number by a text value. That is source of calculation error. So how can we do any workaround?

4. Searching and merge of data

Our special tax rates (Exempt & No tax) are equivalent of 0% tax. We want to save invoices keeping them separately by each of those 3 tax rates. So give it a try and look at different possible solutions: formula based and with Get & Transform tool (known also as Power Query). Before we can continue, we shall add to our tax rates a multiplier, which can provide proper gross amount calculation.

• Return please to tax rates table in Dimensions sheet and click in cell next to table headers (right to [Rate] column). Enter here name of next column - "Percent". When you will confirm, additional column appears in table.

  When you enter any values directly next to right side of table or directly below the table, Excel automatically resizes the table to cover modified cells. This action is stored as additional item on Undo list. If you were writing something without intention to resize table, you can use CTRL+Z shortcut. Table will shrink back to previous size, leaving value entered by you out of table range.

• Input in new column proper percents for each tax code.
  

Now we are ready to combine information from both tables.

Option 1: Function VLOOKUP

ToDo list:

1. Create column, where we will match precentages, found by selected tax code.
2. Correct formula in column [Gross amount], to start using found percent for calculation.

Go back again to sheet with invoices table:

• Replace header name [Tax%] with [Tax Rate]
• In cell next to header [Gross amount] (H2) write "Percent". New column will be created.
• Click in first empty cell in [Percent] column (H3) & click add function button in formula bar. This will allow to observe how particular arguments are filled in.
• Select category: Lookup & Reference, then select function VLOOKUP
• Now we will pass arguments. First one Lookup_value is [Tax Rate] - click in corresponding cell in same row of table.
• Next argument - Table_array - should be set to our supporting table tblTaxRates. It is possible to enter name from keyboard or to select it by mouse click. Press range selection button and go to sheet [Dimensions]
• Show with pointer top left table corner. Mouse cursor will change to small black arrow pointing bottom right. Click in this position.
• Table name will be entered in second argument of VLOOKUP function. Notic, that right next to address field you can see values preview

  {0.23,0.23;0.08,0.08;0.05,0.05;0,0;"Ex..

  Those are values from tax rates table. Columns are separated by coma ",", rows by semicolon ";". Headers are not included.
• Third argument you need to provide is Col_index_num. Input 2 here. Function will look in first column of tblStawkiVAT matching value, but on the output will return value from column index given by this number. In rates table multiplier we need is in column 2 - that is why the number entered.
• Whats left is last argument - Range_lookup. Please input "FALSE" or 0. This parameter decides, if the VLOOKUP should search for approximate value (4 argument = TRUE or 1), or the match should be exact (4 argument = FALSE or 0).

  In most cases there is used FALSE value in last argument. If you want to search for approximated match - values in lookup column must be sorted ascending. Otherwise function can return results far from expected.

• After confirming dialog window by [OK] button, proper multipliers will be visible in each row of table, even where there was text tax codes provided. Let's see steps on animation below:
  
• Now we are left with correction of formula, calculating gross amount. Please click in any cell with gross values and replace column name [Tax Rate] with [Percent] (actual percentage we found for tax rate).
  

This way we were able to join information from 2 different tables. Of course there are many different methods how to do it. One of these is Power Query (known as Get & Transform in recent Excel version). In next episode I will show how to do it using this tool. See you soon!