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...
Read More
Tricks
Excel SumProduct lookup multiple criteria
Excel SumProduct lookup multiple criteria not in first column
In this tutorial, I will explain how to use Excel SumProduct 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 Lookup Function to achieve the same result. Read the below article for details.
Excel Lookup Function lookup...
Read More
Excel Function add year month day hour minute second to date
Excel Function add year month day hour minute second to date
In this tutorial, I will show how to use Excel Function to:
- add year to date
- add month to date
- add day to date
- add hour to date
- add minute to date
- add second to date
I recommend you to read my previous tutorial to understand the concept of time serial.
In the following examples, I will assume A1 contains a date 2015 Jul 1.
Add year to date
Change the Cell Format to dd/mm/yyyy
Assume that we add...
Read More
Access Excel VBA Check Prime Number using custom Function
This tutorial shows a custom Access Excel VBA Function that can check prime number, the Function returns TRUE or FALSE.
Access Excel VBA Check Prime Number using custom Function
In this tutorial, I will show a custom Access Excel Function that can check prime number, returning TRUE or FALSE. Prime number is an integer that can only be divided by 1 or itself (without remainder). Below are some examples of prime number.
1–20
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
...
Read More
Excel Extract Time from Date Time or Extract Date
This Excel tutorial explains how to extract time from Date Time (timestamps) or extract date from Date Time in Excel and how to convert date time to time serial (decimal number).
You may also want to read:
Excel Access VBA extract percentage from text
Access Excel VBA extract number from text or extract alphabet
Access Excel extract file name from file path
Excel Display Time from Date Time
Excel Custom Format
Excel Extract Time from Date Time / extract date from Date Time
Sometim...
Read More
Excel Use Date in IF Condition
Excel Use Date in IF Condition (worksheet solution)
If you use a Text as a condition in IF, you can simply use double quote on the text such as "Text", but to use Date in IF condition, you cannot simply use double quote on a date.
Lets recap how we apply condition on different data type.
To apply condition on Number:
=IF(A1>5,"large number","small number")
To apply condition on Text:
=IF(A1="John","My friend","Stranger")
What about Date? Should we just use double quote as we ...
Read More
Excel time difference in hours minutes seconds
Excel time difference in hours minutes seconds
Excel has a special way to treat Date Time value but most people are not aware of that. You need to understand the meaning of the numeric values that represent Excel Date Time in order that you can freely perform Excel date time conversion to hours, minutes, days, etc. After you understand the meaning of the numerical values, you can easily calculate Excel time difference.
I have written a post that explains conversion between time and numeric ...
Read More
Excel lookup partial text using Vlookup with Wildcard
This Excel tutorial explains how to lookup partial text using Vlookup Function with Wildcard.
Excel Vlookup second matched value or specific occurence
Excel Lookup Function lookup multiple criteria not in the first column
Excel lookup Function lookup multiple criteria
Excel lookup partial text using Vlookup with Wildcard
Many people know how to use Vlookup but not many people know Vlookup allows Wildcard in the lookup value. Lets recap the syntax of Vlookup first.
Syntax of Vlookup...
Read More
Excel Dynamic Print Area
This Excel tutorial explains how to set dynamic print area.
You may also want to read:
Excel dynamic Data Validation list
Excel dynamic data range
Excel graph dynamic data range
Excel Print Area
Print Area is a function to define the worksheet Range you want to print.
To set a Print Area, select a Range, say A1:B6, then click on Set Print Area
Click on FORMULA tab > Name Manager
You will find Range A1:B6 is named as Print_Area, which is the reserved name f...
Read More
Excel Find records of Minimum Date or Maximum Date
This tutorial explains how to find minimum date and maximum date of each record in Excel by sorting and remove duplicate.
You can find more tutorials at http://Access-Excel.tips
Find records of Minimum Date or Maximum Date, Why?
Find records of minimum date or maximum date is an essential skills in employee records. Some have data use an unique key such as employee ID plus a date as the unique record. For example, when Peter (Employee ID 001) was hired on 1/1/2014 and then terminated on 1/5...
Read More