VLOOKUP is my first love of Excel, it is great function to get the corresponding values from a table easily, however there is situation where we might want to get the cell color and font color along with lookup value. VLOOKUP is a function which will return only values, but not the cell formats. Thankfully Excel allows […]
Articles Tagged: Lookup Formulas
Excel Function: MATCH() Explained
Excel MATCH function examples allow us to see how the MATCH function can be used, and understand what scenarios it could help us. Basically, the MATCH function was designed to return a relative row reference (relative to the array in which data is searched for) for data that you specify. If searching for a number, […]
Excel Tip : How to Generate Unique Usernames using the IF() and COUNTIF() Functions
Today let’s learn how to generate unique usernames in Excel, even when you have duplicates in your data. Formulas Used 1) IF( condition, [value_if_true], [value_if_false] ) Learn More about IF() Parameters or Arguments condition The value that you want to test. value_if_true Optional. It is the value that is returned if condition evaluates to TRUE. […]
How to Flag Multiple Matches In Your VLOOKUP Formula?
This article will explore the Excel VLOOKUP Formula and show you how to combine it with an IF statement to flag if there are multiple lookup values matches in your data set. VLOOKUP is really great for pulling in data from a larger table in Excel, but sometimes in a larger data sets there may […]
Excel Formula : SUMPRODUCT () for Advanced Users
When I first learned about the SUMPRODUCT formula in Excel , I almost dismissed it as a useless formula used by only few users. How often would anyone need to multiply 2 or more groups of numbers together and add up the results!? To get to the story, we know that formula is keyed in as: […]
9 Tips to Write Lookup Formulas More Effectively – Excel Tip
It is not breaking news, but something that anyone working on an Excel Report using formulas and functions, will know the keys of successfully using lookup formulas. They are vital for making almost any spreadsheet or dashboard. That is why when you learn how to extract information from Excel tables, you will be truly excited, […]
How to Define a Named Range in Excel? What are the top 3 uses of Named Ranges?
Another underused feature of Excel is Named Ranges. Named Ranges allow you to assign a meaningful name to a cell or a range of cells and then use that name instead of the actual cell reference in your formulas. A name is easier to remember when you are constructing your formulas.
How to get the Last Numeric Value in a Column?
Returning the final value in a column is particularly useful for spreadsheets that involve running balances, where you want to reference the final balance regardless of whether it is the smallest or largest value in the column.
How to VLOOKUP with Multiple Criteria in Multiple Column
VLOOKUP is definetly a great tool to get the data from tables, however it has limitations: it can only work with one criteria for matching information. What If you need to refer two or more conditions to match a specific piece of data, VLOOKUP will only get the first match in the data. Fortunately Excel has functions called INDEX and MATCH that works same as VLOOKUP but with multiple criteria. Here’s a quick tutorial to help you learn how…
Solutions to Three Common Problems when Using VLOOKUP()
This article addresses some common issues you may encounter when you use the VLOOKUP function.