The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to understand and troubleshoot. Return multiple values vertically Array formula in C8: =INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), […]
Problem: How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, but only want to find the Min and Max on the largest/top 100 non-duplicate values. Solution: Array formula in cell D11: =MAX(IF((COUNTIF(Table1[Value],Table1[Value])<>1)+(Table1[Value]=””),””,Table1[Value])) Array formula in cell D12: =LARGE(IF((COUNTIF(Table1[Value],Table1[Value])<>1)+(Table1[Value]=””),””,Table1[Value]),100) Formula in cell D15: =MAX(Table1[Value]) Formula in […]
What INDEX MATCH MATCH offers you is a more powerful version of the formula. Instead of just a vertical lookup, INDEX MATCH MATCH allows you to perform a matrix lookup, which is also known as a two-way lookup
HLOOKUP is essentially the horizontal version of VLOOKUP. The primary reason for using HLOOKUP in Excel is for when you have key data points arranged horizontally across the top of your table. This usually happens when you are dealing with time series data; people have a tendency to list lookup values horizontally with this type of data.
Excel Count Functions Examples show how to count Excel cells with numbers, text, blanks, or cells that contain specific words or other criteria. Count Cells with Numbers — Excel COUNT The COUNT function will count cells that contain numbers. Its syntax is: =COUNT(value1, value2,…value30). The arguments (e.g. value1) can be cell references, or values typed […]
Excel’s Operators CHEAT SHEET Believe it or not, Excel has 16 infix operators, 5 match fix operators, 3 prefix operators and a postfix operator. Infix Operators + Addition – Subtraction * Multiplication / Division ^ Exponentiation > is Greater than < is Less than = is Equal to >= is Greater Than or Equal to […]
VLOOKUP MATCH is one of several possible lookup formulas within Microsoft Excel. This tutorial assumes you already have a decent understanding of how to use VLOOKUP. If you do not, please click here for a beginner’s tutorial on VLOOKUP.
Objective
VLOOKUP MATCH is an improved variation of your basic VLOOKUP or INDEX MATCH formula. Using VLOOKUP MATCH allows you to perform a matrix lookup – instead of just looking up a vertical value, the MATCH portion of the formula turns your column reference into a dynamic horizontal lookup as well. VLOOKUP MATCH is mainly useful for situations where you intended to perform heavy editing on your data set after you’ve finished writing your formula. This is because VLOOKUP MATCH gives your lookup formula insertion immunity; whenever you insert or delete a column within your lookup array, your formula will still pull the correct number.
The key difference between using VLOOKUP MATCH versus the basic VLOOKUP formula is that, in addition to your vertical lookup value (what you’ll be looking up down the left side of your table) you’ll also have a column lookup value (what you’ll be looking up across the top of your column headings).
The Syntax
VLOOKUP and MATCH are the two formulas that are combined to perform this lookup. We’ll look at each of the formulas separately before putting them together. The primary formula we’ll be using is VLOOKUP:
=VLOOKUP ( lookup value , table_array , col_index_num , [range_lookup] )
To use this formula, you’ll need a lookup value and a table array. (We’ll address the column index number later and since we are not performing a range lookup, we can leave that part of the syntax blank) In the example below, the lookup value is ID number “5” and the table array is the green box surrounding cells B6:F14.
Next we have the MATCH formula:
=MATCH ( lookup value , lookup_array , [match _type] )
The match formula returns a position number based on your lookup value’s location within the array you’ve selected. To use this formula you’ll need both a lookup value and a lookup array. (The match type parameter should be left blank – doing so tells Excel that we want an exact match). In the example below, the lookup value we’ll be using is the State of “WA” and the lookup array is the orange box surrounding cells B6:F6.
Putting it Together
The key to VLOOKUP MATCH is that we are replacing the “column index number” syntax of VLOOKUP with the MATCH formula. Perform this combination using the following steps:
Step 1: Start by typing your VLOOKUP formula as you normally would, inputting the proper lookup value and table array for your lookup; in this example the lookup value is ID number “5” and the table array is the green box surrounding cells B6:F14.
Step 2: When you get to the column index number input, instead of typing in a hard coded number, start typing in the MATCH formula
Step 3: For the MATCH formula’s lookup value, select the cell containing name of the column you want to return from; in this example we want to return a State, so we click on it
Step 4: For the MATCH formula’s lookup array, select the row headings of your table array; in this example it is the orange box surrounding cells B6:F6.
Step 5: Close off both your MATCH formula and your VLOOKUP formula with two parentheses (doing this simply confirms for Excel that we want an exact match for the MATCH formula and that we don’t want to use a range lookup for the VLOOKUP)
How it Works
The MATCH formula we created returns the value 4. Therefore, based on how we arranged the syntax, the VLOOKUP MATCH in this state is basically performing the same function as a VLOOKUP with a column index number of 4.
However, the key difference is that this column reference is now dynamic. If I insert or delete a column from my lookup table, my return value will stay the same. See below for an example of the difference in return values between VLOOKUP and VLOOKUP MATCH after inserting a column.
This function will allow you to compare one file to another. Copy code below and paste directly into your VB project. Sub CompareTextFiles() ‘********************************************************** ‘PURPOSE: Check to see if two files are identical ‘File1 and File2 = FullPaths of files to compare ‘will compare complete content of the file, including length of the document (Bit […]
You have probably been doing “if” logic ever since you were a toddler: If this is true, then this; otherwise, this. IF statements are great for solving undesirable, but predicable, problems with error conditions. IF statements are good at solving problems with negative days or time. If you’ve ever tried to subtract a later date […]
Excel’s AVERAGE() function evaluates zero values. If you want to exclude zeroes, use these alternatives. Excel’s AVERAGE() function considers zero values in its calculations, which is probably what you’d expect, but not necessarily what you want. Over the years, I’ve seen many formulas that ignore zero value and most are more convoluted than they need […]