 ## Blog

VLOOKUP is a widely used Excel function. It is commonly found occupying entire columns of a spreadsheet, having been entered in that very first cell and copied down. In addition to the performance penalty that such spreadsheets pay, if the formula has been mis-copied, it can be difficult to see these errors.

Schematiq offers a concise and efficient alternative to VLOOKUP, that applies to all rows of your table with a single formula.

Sounds too good to be true?

First, let’s start with a common scenario:

You’ve been following a good structure and keeping your price list and order lists separate (hopefully in a database, but if you’re using Excel for this, please see below*).

You have two tables of data shown below; one containing customer orders, and a second containing your price list. You’ve been asked to produce a report showing the total costs – how would you do this? If you’re thinking VLOOKUP, this would work. (And if you’re thinking INDEX and MATCH, even better, as these are more efficient for larger lookup and result tables).

We will solve this problem twice here, once using VLOOKUP, and secondly with a single Schematiq function call. The Schematiq approach improves efficiency in the calculations, and will eliminate the need to copy your VLOOKUP formula down an entire column.

In the process, we will show 5 areas where highly-used Excel operations can be similarly achieved using Schematiq functions:

1. Step 1 replaces Excel's many VLOOKUP formulas with a single tbl.Join Schematiq function.
2. Step 2 shows how copying formulas to calculate a new column can be replaced by a single tbl.CalculateColumn Schematiq function.
3. Step 3 will show that columns can be selected (or removed) from the output using a single formula, analogous to hiding or deleting columns in Excel.
4. Step 4 will rename the columns of our resulting data set, in the same way you might change a column header in an Excel data-set.
5. Step 5 will return the values back to our Excel sheet, much like pasting values in Excel to produce a report.

### STEP 1 – Retrieve the cost per item

Using Excel’s VLOOKUP, we can look in the table B6:C8 for the item contained in C13, and return the associated cost.

E13: = VLOOKUP(lookup_value, table_array, col_index_num)

= VLOOKUP(C13, \$B\$6:\$C\$8, 2) We then need to copy this formula down every row of our table. Fortunately in our example there are only 5 rows, but this can easily extend in to the thousands of rows. In cases of large tables, VLOOKUP can become slow, and a better formula to use is

= INDEX(C6:C8, MATCH(C13, B6:B8))

We can see that Schematiq is suggesting this improvement in Viewer. Clicking on the lightbulb will update the cell formula with Schematiq's recommended one.

Now we will achieve the same result using Schematiq's built-in functions. First let's create a Schematiq table for each data-set:

C20: = tbl.Create([data], [columnNames])

= tbl.Create(B6:C8, B5:C5)

C21:

= tbl.Create(B13:D17, B12:D12) When clicking on C21, the Schematiq Viewer shows that we have correctly loaded these tables.

We will now use Schematiq’s tbl.Join function that combines two tables, based on the values in the common "Item" column:

C23: = tbl.Join(table, withTable)

= tbl.Join(C21, C20) This action of joining the tables is equivalent to applying the VLOOKUP function on all rows, but Schematiq does this in a single action – even if the table had thousands of rows. The result is stored in a single cell, and no formula copying is required.

### Step 2 – Add a new calculated column

To calculate a new total cost column in Excel, we would write the following formula in cell F13, and then copy this down all of our rows again, making sure that our cell references are all relative this time:

F13:

= D13 * E13 To format this table, we would then paste these values in to a separate sheet; rename and delete any columns that were no longer required, and save our final report.

These steps are manual and, in the case of deleting columns containing copied data, somewhat irreversible (if we really did need a column back, we would have to undo or re-copy).

Instead, let’s complete this process using Schematiq to reorder and select columns. Since these steps involve Schematiq function calls, they can be easily adjusted by changing the function parameters.

First we use tbl.CalculateColumn to create a new “Total Cost (£)” column by multiplying “*” the two input columns:

C25: = tbl.CalculateColumn(table, [inputColumnNames], function, [outputColumnNames])

= tbl.CalculateColumn(C23, D25:D26, "*", "Total Cost (£)") This has returned our updated Schematiq table, again using a single-cell formula.

### Step 3 – Selecting and ignoring data columns in a table

We can use Excel to select columns by either deleting or hiding fields.

Schematiq does this with a function call to tbl.SelectColumns:

C28: =tbl.SelectColumns(table, columnNames)

= tbl.SelectColumns(C25, D28:D29) This can be easily adjusted to different requirements, simply by editing the column headers in D28:D39, or adding additional ones – no need to hide or delete columns from your Excel sheet!

PRO-tip: You can use tbl.SelectColumns to change the order of columns too, by re-ordering the columnNames range.

### Step 4 - Renaming columns

In Excel we can do this by changing the column headings of our summary table.

Similarly with Schematiq we'll create a template of what our summary table should be, and use this range for our renamed columns:

C31: = tbl.RenameColumns(table, oldNames, newNames)

= tbl.RenameColumns(C28, tbl.ColumnNames(C28), B33:C33) ### Step 5 – Presenting your report

Schematiq tables are very useful for storing and formatting all the data from a single Excel cell.

For our final step, we want these values back in our Excel sheet for presenting.

To do this, Schematiq provides the tbl.GetValues function (this can be called as an Excel array function by selecting the entire range and entering the function with CTRL-SHIFT-ENTER):

B34:C38: = tbl.GetValues(table) as an array function

{ = tbl.GetValues(C31) } We now have our analysed and formatted table back in Excel, and this completes our example of using Schematiq for data column analysis in Excel.

For more on this topic, see the PRO-tips below, or download a copy of the example workbook here. This will require an active Schematiq licence – please contact us if you would like a trial.

* Schematiq has built-in functions that can retrieve data from a variety of sources, so you don’t need to be storing this made-for-a-database type data in Excel. Instead, you can keep them in your preferred database, and load your queried tables directly in to Excel using these functions. If you want to know more about this, please ask us.

## PRO-tips

### 1) Use sq.Name to clarify the data tables in the Excel sheet:

Schematiq tables are assigned a default name like “table(23)”. For important tables, you can assign a custom name using sq.Name:

C20: sq.Name(name, value)

= sq.Name("Price Table", tbl.Create(B6:C8, B5:C5)) ### 2) Get LOOKUP values for multiple columns

Using Excel alone, a new column added to the prices data would require a new VLOOKUP column.

However, Schematiq’s tbl.Join function is able to add multiple columns at once. For example if the unit is a separate column (as below), we only need to change the Prices table, to include the extra column. 