Excel Vlookup second matched value or specific occurrence

Posted on March 25, 2015September 17, 2017 by Wyman W

This tutorial explains how to write a custom VBA Excel Function to vlookup second matched value or specific occurrence. You may also want to read: Case sensitive lookup Excel Lookup Function lookup multiple criteria not in the first column Excel lookup partial text using Vlookup with Wildcard Excel lookup Function lookup multiple criteria Excel Vlookup second matched value or specific occurrence In traditional Vlookup, you are allowed to vertically lookup the first value that ma...

Excel Indirect Function to convert text to Reference

Posted on March 25, 2015March 25, 2015 by Wyman W

This tutorial explains how to use Excel Indirect Function to convert text to Reference, and how to use in Data Validation. Excel Indirect Function Excel Indirect Function is to convert text to Reference (or cell value). For example, formula =A1 returns A1 value because A1 is considered as Reference, but ="A1" is considered as Text. Excel Indirect Function can convert the "A1" Text to become Reference. One popular use of Excel Indirect Function is to use in Data Validation where you convert ...

Excel Address Function to convert column number to letter

Posted on March 24, 2015April 2, 2015 by Wyman W

This Excel tutorial explains how to use Excel Address Function and how to convert column number to column letter. Excel Address Function Excel Address Function is used to combine column number and row number to return Cell address in Text format. Excel Address Function is most useful is converting column number to column letter, or use with Indirect Function to convert the Text address to Range Object. Syntax of Excel Address Function ADDRESS( row, column, [abs_num], [a1], [sheet_text] )...

Excel Subtotal Function to calculate Auto Filter value

Posted on February 26, 2015 by Wyman W

Excel Subtotal Function to calculate Auto Filter value Excel Subtotal Function is a collection of aggregate function (Sum, Average, Min, etc), in which you can specify in argument which aggregate function to use. There are two major differences between Subtotal Function and normal aggregate Functions 1. Subtotal Function can calculate base on filtered value in Auto Filter, while normal aggregate Function calculates all values in a Range regardless of the criteria in Filter. 2. Subtot...

Excel Hyperlink Function to link to other cell, open website, open document

Posted on February 25, 2015February 25, 2015 by Wyman W

What does Excel Hyperlink Function do? Excel Hyperlink Function is to create a hyperlink in a Cell. The hyperlink can be linked to external website or a Cell in a worksheet. In Excel VBA, there is a similar Method called FollowHyperlink, but it actually “go to” hyperlink instead of creating a link for user to click on. Syntax of Excel Hyperlink Function HYPERLINK(link_location, [friendly_name]) link_location The path of the file you want to link to. To link website, type "http://...

Excel Custom Format in Format Cells

Posted on February 24, 2015May 13, 2015 by Wyman W

This Excel tutorial explains how to use Excel Custom Format in Format Cells to format date, time, number, text, conditional formatting, leading zero, etc. You may also want to read: Excel Extract Time from Date Time Excel Display Time from Date Time What is Excel Custom Format? Excel guesses the most appropriate format for the cells when you type something in a Cell. For example, if you type 31/1/2014, Excel guesses it would be a Date, Format is dd/m/yyyy, but it would not guess it is...

Excel maximum number of digits workaround

Posted on February 24, 2015 by Wyman W

Excel maximum number of digits In Excel spreadsheet, there is a limit for storing a number in a Cell, which is 15 digits (15 numbers) regardless of whether the numbers are decimal places. Excel call this "15 significant digits of precision" which adheres to "IEEE 754". It is not difficult to understand why Excel limits the number of digits, because some numbers are infinite, such as π number (3.1415....), it is not possible to use infinite computer memory to store infinite numbers. We need to c...

Auto Open Excel with Windows Task Scheduler

Posted on February 23, 2015February 23, 2015 by Wyman W

Auto Open Excel with Windows Task Scheduler Windows Task Scheduler allows you to trigger an event in Windows when specific criteria is met. In this tutorial, I will not detail what you can do with Task Scheduler (since there are too many features), instead I will focus on how you can auto open Excel with Windows Task Scheduler. Since I am using Chinese Windows but I want to write this article in English, all the screenshots are copied from the below website. http://www.sevenforums.com/tut...

Excel 3D Sum to Sum the same Cells in different worksheets

Posted on February 23, 2015June 6, 2017 by Wyman W

This Excel tutorial explains how to use Excel 3D Sum to Sum the same Cells in different worksheets. You may also want to read: Excel VBA loop through worksheets in the same workbook What is Excel 3D Sum Excel 3D Sum to Sum the same Cells in different worksheets. You can achieve the same task without 3D Sum, but 3D Sum can simply your formula. The name "3D Sum" is not a Function name, you cannot use a Function like =3DSum() , it is just a special way to use SUM Function. Before you know ...

Excel Lookup Function lookup multiple criteria not in first column

Posted on February 19, 2015February 19, 2015 by Wyman W

Excel Lookup Function lookup multiple criteria not in first column In this tutorial, I will explain how to use Excel Lookup Function to lookup multiple criteria while the lookup value is not in first column of lookup range. The reason for writing this article is that Vlookup can only lookup one criteria and lookup value must be in the first column of lookup table. You can also use SUMPRODUCT Function to achieve the same result in the below article. Excel SumProduct lookup multiple crite...