Not since the great territory battles of 7-Eleven Vs. Quiktrip, has there been such an unparalleled rivalry: “VLOOKUP or INDEX/MATCH?“A seemingly innocent question, when asked among Excel users, gives you a general perception of one another. There is one function’s inability to turn left and the others reliance on multiple functions for use. One’s perceived simplicity vs. the others perceived complexity. Are we so divided, though that we are unable to recognize the benefits found within each function? I’m unsure.. but I know neither function is willing to surrender their position without a fight.
Download Workbook
#TEAMVLOOKUP
Function: VLOOKUP
Describe It:
- A reference lookup for vertical information retrieval.
Syntax:
- =VLOOKUP((lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value is the value in which you want to reference for lookup
- table_array is the range of cells in which you will search for the lookup_value
- col_index_num is the column in which to return a value (left most column starts at 1)
- [range_lookup]
- TRUE is an approximate match indicating return a value that is similar to the lookup_value
- FALSE is an exact match indicating return a value that is exact to the lookup_value (not case sensitive)
Ask The Question: What state are fictional products being sold in?
Make It False:
=VLOOKUP(“Fictional”,tblProduct[[Product Type]:[State]],3,FALSE)
Talk It Out: Even though there are four columns possible the table_array you specified is only Product Type thru State. For this example the left most column Product Type will begin your column count at 1, the State category is found two columns away so the column in which we wish to return information is that of col_index_num 3. If multiple items of the same type are found, it will return the first corresponding matched value from top to bottom.
Ask The Question: What is the total commission percentage for a sale of $61.50?
Requirements:
Values will need to be sorted in Ascending order for the function to work correctly.
Make It True:
=VLOOKUP(61.5,tblSales[#All],2,TRUE)
Talk It Out: The formula will review each item within the left most column until the lookup_value returns a value that is equal to or less than the item within the lookup column. If the item is less than the current value it will return the index position to that of the previous value’s position. As shown in the example above – $61.50 is greater than $50.00 so it evaluates the next item $75.00 and determines that it is less than $75.00, at this point it returns to the previous value and it’s corresponding index position.
#TEAMINDEXMATCH
Function: INDEX
Describe It:
- A reference lookup for the intersection of a row and or column retrieval.
Syntax:
- =INDEX(array,row_num,[column_num])
- array is the range of cells or an array constant
- row_num is the row number in your array in which you want to reference
- [column_num] is the column number in your array in which you want to reference (optional)
- [area_num] is the array number if multiple ranges used (optional)
Make It:
=INDEX(tblRegion[#All],10,4)
Talk It Out: The formula will create an index of the left most column, at which point it will search for the item in the 10th position. Once located, it will then return the value from the column_num 4.
Caution
The position of the row_num does not reference the worksheet but the array selected. For example, if you were to use a formula similar to the one below, the 1st indexed position would start on the 4th row.
=INDEX($A$4:$C$9,2,3)
Function: MATCH
Describe It:
- A reference lookup for the intersection of a row and or column retrieval.
Syntax:
- =MATCH(lookup_value,lookup_array,[match_type])
- lookup_value is the value in which you want to reference for lookup
- lookup_array is the range of cells in which you will search for the lookup_value
- [match_type]
- -1 – “Less Than”:
- 0 – “Exact Match”:
- 1 – “Greater Than”:
Requirements:
Values must be sorted in Descending order for the function to work correctly.
Make It (Less Than):
=MATCH(3600,tblRegion[Amount],-1)
Talk It Out: The -1 match type finds the smallest value within the lookup_array that is greater than or equal to the lookup_value. In the example above, the item $3,527.83 is less than our lookup_value of $3600.00. So, the smallest value that is greater than $3600.00 is $3,809.88, which will return an index position of 16.
Make It (Exact):
=MATCH(“Speakers”,tblRegion[Product Type],0)
Talk It Out: The 0 match type finds the exact matched lookup_value within the lookup_array. As shown in the example above, it will return the index position of the first exact match found even though multiple values exist.
Requirements:
Values must be sorted in Ascending order for the function to work correctly.
Make It (Greater Than):
=MATCH(3600,tblRegion[Amount],1)
Talk It Out: The 1 match type finds the largest value within the lookup_array that is less than or equal to the lookup_value. In the example above, the item $3,527.83 is less than our lookup_value of $3600.00. So, the largest value less than $3600.00 is $3,527.83, which will return an index position of 2.
We’re not out of the woods yet with INDEX and MATCH.
Nested Function: INDEX(MATCH())
Ask The Question: What Product Type is sold in the State of CA?
Make It:
=INDEX(tblRegion[Product Type],MATCH(“CA”,tblRegion[State],0))
Talk It Out: The INDEX function uses the Product Type column as the functions array index. We then nest the MATCH function to review the State column and return the row_num when a specified lookup_value condition is met.
If it sounds confusing reading it.
It felt confusing writing it.
ELI5: We know the MATCH function allows us to search a column to find where a conditions criteria is met, whereas the index function only uses numerical return positions. So in the above scenario, let’s read the formula from the inside out. We first find the MATCH of the state “CA” at the indexed position of 6. We then tell our INDEX function to review the Product Type column and return the 6th indexed item. I would mark the -1 and 1 match types as NICE-TO-KNOW, but the majority of your time will be spent using EXACT match type when it comes to data. Trust me.
#TEAMINDEXMATCH. But, I am sad coz #Vlookup is an accidental hit.
With Power Query it’s putting them both out of business and making us more efficient at the same time. Soon this won’t even be an arguement anymore 🙂 #TEAMINDEXMATCH
I’m not on either “team” — I believe in using whatever works best and easiest. I think it’s nice having OPTIONS so we’re not stuck with just one or just the other.
That’s one of the best parts about Excel, is the ability of choice and to ultimately end up at the same solution. I’m sure there’s still people out there who would swear by SUMPRODUCT as opposed to using either VLOOKUP or INDEX/MATCH as well. Though I do see the pendulum swinging to a more singular direction as they continue to build more robust features into Power Query when it comes to managing and cleansing data.
Don’t forget you can use the MATCH function to provide the column number for the VLOOKUP to provide a semi-flexible lookup.
Also remember INDEX can be used to create range references whereas VLOOKUP can’t.
And use 0 on the end of an exact match VLOOKUP instead of FALSE – does the same thing but quicker to type.
Typing “F” and clicking “False” is only one stroke more and easier to remember. My problem is that I think an exact match should be True and a “closest” match should be False.
I completely agree on some of the nuances found within Excel, they designed the function from more of a computer programmers perspective using BOOLEAN as opposed to designing it for a layman’s use.
And don’t forget that both VLOOKUP and MATCH can use wildcard characters when the lookup value is text.
When you can’t trust in normalized data – “*” – to the rescue!
You can do a VLOOKUP to the left if you combine the VLOOKUP with a CHOOSE formula. It can be done like this:
=VLOOKUP(A1,CHOOSE({1,2},$J$1:$J$6,$I$1:$I$6),2,0)
where the lookup range is in Col J & the return range is in Col I
I do, however, use INDEX/MATCH a lot, particularly with large data sets.