The Excel IF Formula can be extremely powerful when used in conjunction with the VLOOKUP formula. In this post we will use these two formulas to determine if a condition is true by looking up a customer number against the payment records table to see how many customers are using credit as their default terms. Once we've done this, we'll add up the number of customers with default payment terms of credit using the COUNTIF formula.
- =IF(logical_test, [value_if_true], [value_if_false])
- =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- =COUNTIF(range, criteria)
The Excel VLOOKUP IF Statement will be used in this post to merge information from two different tables. An abbreviated customer table as well as a customer default payment terms table. The IF statement will be used to determine "if" and which customers are setup to have credit as their default payment term. We will return a value of True or False for each customer record. Once this is completed, we will have another field within our little dashboard that tells us the number of customers that have a default payment terms of credit using the COUNTIF formula.
How to Use the Vlookup If Statement
In this workbook we're going to have two tabs. One tab will include three different fields where the third field is the CREDIT_DEFAULT_PAYMENT_TERMS which is the field we are wanting to get a result for (this is the customer table). The second table will have customer information that shows the customer default payment terms by customer_id (this we will call the payment records table).
CUSTOMER
- CUSTOMER_ID
- CUSTOMER_NAME
- CREDIT_DEFAULT_PAYMENT_TERMS
PAYMENT RECORDS
- CUSTOMER_ID
- CUSTOMER_NAME
- DEFAULT_PAYMENT_METHOD
Below is a diagram outlining by step how to use a VLOOKUP statement within an IF statement/formula to determine if a certain condition is true from a different table. Here we are trying to determine if the customer has default payment terms of credit. If the customer has default payment terms of credit, we will display true if not, we will display false.
Please download the spreadsheet at the end of the post and open up the diagram below by clicking on the image to understand how the formulas work.
- The IF statement allows us to check if a certain scenario/condition exists and then allows us to display two different values (one if the condition is true and one if it is false).
- The VLOOKUP formula allows us to use a lookup value which in this case is B3 (CUSTOMER_ID = 000001).
- B3 is the lookup value in the customer table that we need to match against the payment_record table.
- The value that you are looking up in a VLOOKUP statement must be located in the first column of the table_array. In this case if we are trying to find customer 000001 (B3) within the payment_record table we must look up the value within the column/field that matches the customer_id. You will then go further and also select the associated columns within the payment_record table that you want to find and display on the original table (customer table). Table_array = B:D.
- 3 is the column number within the table array (i.e. column D - Default_Payment_Method). This will be the value displayed once we find the lookup value in column B. In this example, once we find customer 000001 in column B, we will then go across to column D and find the corresponding value of that row (here the value is Credit). The value of false means the look up value must match exactly the value found in the table_array.
- The = symbol says if the value returned from our VLOOKUP= "Credit" then.... Note that if the logical test value is a text string, the value must be in quotations. If it is a number no quotations are required.
- "TRUE" is the value that is going to be displayed in our result (D3) if the value found during the VLOOKUP is = "Credit". "FALSE" is the value that is going to be displayed if the VLOOKUP value is anything but "Credit".
Now that you've went through each step, after you've filled out the formula in your first table cell, all other cells will automatically update with the new formula and provide a true/false result.
How to Use the COUNTIF Formula
The COUNTIF formula in this post will be used to count the total number of records where the CREDIT DEFAULT PAYMENT TERMS are true. This formula will tell us how many customers we have that currently have default payment terms of credit. You will see that we are using a table for the customer table which will allow the COUNTIF statement to automatically update should new records be added to our customer table.
- Table2[[#All],[CREDIT DEFAULT PAYMENT TERMS]]: This part of the formula is the range of the cells that we would like to count. Here we are using a table and all cells corresponding to the CREDIT DEFAULT PAYMENT TERMS field/column.
- "*" & "TRUE" & "*": The ampersands and the stars are used to find text. If we were looking for a value, these would not be needed. This part of the statement now is saying if you find the value true, add 1 to our count within our range.
Now that both parts of the formula are filled out, close the brackets and hit enter. This will give you the total count of customers that have default payment terms of credit.
If you have any further questions, comments or suggestions, please drop me a line in the comments and I'll be glad to respond.
Download the Excel Workbook
Free Excel File Download!The Excel file is indeed free. When you click the 'GET IT FREE' button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you'll need is your email address!
Hi Brad,
Since you’re returning TRUE/FALSE you could simplify your IF VLOOKUP formula to make use of Boolean logic like so:
=VLOOKUP(B3,’PAYMENT RECORDS’!B:D,3,FALSE)=”Credit”
This will return a TRUE or FALSE outcome without the need for IF.
But then you wouldn’t have had the opportunity to teach IF 🙂
Cheers,
Mynda.
Hi Mynda,
You got love how there’s never just one way to get what you need in Excel! That’s a great example of simplification – thank you for that. I hadn’t realized you could use boolean logic within the formula.
The If statement is always a classic to learn but if we’ve learned anything from this, it’s that often times there is a more efficient way to do things.
Cheers,
Brad
Your tutorials are impressive! Please have a look at Add-in below which may be of help to your audience. The full version – coming out soon – will display unique formulas and functions in any workbook
http://www.spreadsheet1.com/syntax-and-usage-navigation-add-in-for-excel-2013-functions.html
I love the add-in. This is a great idea especially for someone like myself who is building tutorials. Having a quick reference to what functionality is available in each Excel version is Key.
Thanks for stopping by!
Helpful Site