Posts

VLOOKUP using Approximate Match

In a lot of cases, we use VLOOKUP to find exact matches, based on some kind of unique id. But there are many situations where we can perform VLOOKUP using Approximate Match. A classic case is using VLOOKUP to find a Grade for the students where marks are provided.

Suppose we are provided with Test scores of students and have been asked to find the grades for each students base on different slabs of scores. In such cases, we generally use nested IF and the formula goes too long.

We can replace the same with VLOOKUP using Approximate Match by using TRUE or 1 in the last argument that is [range_lookup].

vlookup-using-approximate-match

[range_lookup] is the most critical or we can say the most important argument of VLOOKUP. If we use TRUE, then it finds the closest minimum match from the Master Table.

The most important task to get the desired result is to prepare the table_array in a smart, VLOOKUP friendly format and in the sorted in ascending order.

Generating a List of File Names

This tip describes how to retrieve a list of file names in a folder and display them in a worksheet.This technique uses an Excel 4 XLM macro function in a named formula. It’s useful because it’s a relatively simple way of getting a list of file names into a worksheet – something that normally requires a complex VBA macro.

Start with a new workbook and then follow these steps to create a named formula:

  1. Choose Formulas Tab ➜ Define Name to display the New Name dialog box (CTRL + F3).
  2. Type FileList in the Name field.
  3. Enter the formula in the Refers To field =FILES(Sheet1!$A$1)
  4. Click OK to close the New Name dialog box.

filelist_name_manager

Note that the FILES function is not a normal Worksheet Function. Rather, it’s an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and a file specification) and returns an array of file names in that directory that match the file specification. A normal worksheet formula cannot use these old XLM functions, but named formulas can. After defining the named formula, enter a directory path and file specification into cell A1.

For example:

  • F:\Future in Hands\Training\*  to display all files
  • F:\Future in Hands\Training\*xl* to display all Excel files

Then this formula displays the first file found: =INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Below figure shows an example. The path and file specification is in cell A1. Cell A2 contains this formula, copied down the column: =INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. When you change the directory in cell A1, the formulas update to display the new file names.

generating-a-list-of-file-names

Note

If you use this technique, you must save the workbook as a macro-enabled file (with an *.xlsm or *.xls extension).

 

Join multiple text quickly

We sometime comes with the challenge to join multiple text in Excel. The first thing that comes into our mind is to use the CONCATENATE Function. The major challenge in using this function that we cannot select a continuous Range such as CONCATENATE(A1:A10). To get the result we need to specify all the cells individually.

To do this quickly and smartly, we can use TRANSPOSE with CONCATENATE Function. Below are the steps for doing this. Let say the cells you want to combine are in A1:A10.

  1. In a blank cell, where you want to concatenate all the values type =CONCATENATE(TRANSPOSE(A1:A10))
  2. Don’t press enter yet.
  3. Select the TRANSPOSE(A1:A10) portion and press F9. This replaces the TRANSPOSE(A1:A10) with its result.
  4. Now remove curly brackets { and }
  5. Press Enter