![]() When VLOOKUP performs a range lookup, it scans down the first column. Yikes! What is going on here? Well, as we discussed in Hack 2, when we set the 4th argument to TRUE, we are really asking Excel to perform a range lookup. When we inspect our formula, it actually returns the value for the East Region, as shown below. So, we sort the table in ascending order by the first column. We discussed this a while back in Hack 1. Drats! Same error, as shown below.Īh, but then we remember something about the sort order mattering. TRUE is described as Approximate Match, as shown below. We discussed this long ago in Hack 3.īut, we know that the 4th argument has two possible choices. Since “North Region” in B7 doesn’t match exactly to “North Region Subtotal” found in the table, we get an error. That tells VLOOKUP to perform an exact match. It makes sense because we used FALSE (or 0) for the 4th argument. So, we start off with something like this: =VLOOKUP(B7, Table1, 2, FALSE)Īnd, we hit enter, and get #N/A as shown below.Īfter reflecting on this, it makes sense. Now, we’d like to write a formula in C7 that retrieves the corresponding amount from Table1, shown below. We’ve stored the region in cell B7 as shown below. So, let’s just confirm what we are talking about. Note: depending on your version of Excel, you may have XLOOKUP as an option … more info here: Video ![]() I’ve created a video demonstration and a written narrative for reference. ![]() So, what exactly is an approximate match? Well, as you may have guessed, we’ll dig into that and hack the 1st argument to accomplish our true objective: a partial match. FALSE means exact match and TRUE means approximate match. We started this series by looking at the 4th argument. Let’s say we want VLOOKUP to match the lookup value “North Region” with “North Region Subtotal” stored in the lookup range. ![]()
0 Comments
Leave a Reply. |