By only pointing to AA and BL, the XLOOKUP creates fewer formula dependencies with =XLOOKUP(A2,AA2:AA999,BL2:BL999). If anything inside the table changes, Excel’s intelligent recalculation engine will recalculate all of the VLOOKUP formulas. Although your VLOOKUP formula is really only looking at columns AA and BL, Excel will interpret the formula as having a dependency on anything from AA to BN. You need to get something from the 38th column using =VLOOKUP(A2,AA2:BN999,38,False). Less-frequent calculations: Imagine an odd scenario where your lookup table starts in AA2 and extends 40 columns to the right. Let’s take a look at some of the highlights. There are many other improvements brought about with XLOOKUP. Because of careful use of the dollar signs, the third argument will automatically move from AB$2:AB$99 to AC$2:AC$99 when you copy it to the right. For example, if your lookup table is in AA2:AM99, you might write =XLOOKUP($A2, $AA$2:$AA$99, AB$2:AB$99). With XLOOKUP, you specify cell addresses for lookup value, where to search, and what to return. Thus, you’re left with either editing the copied formula 11 times or applying some Excel trickery like using COLUMN(B:B) or a helper row to get the return column to move to the right. If that formula is in column B and you copy it to columns C, D, E, and so forth, Excel doesn’t know to increment the 2 to 3, 4, 5, and so on. The 2 in the third argument says that you want to return the second column from the table. Consider the formula =VLOOKUP($A2,RateTable,2,False). Say that you must do 12 columns of VLOOKUPs to return rates for January through December. XLOOKUP replaces the integer with specific cell addresses. The third argument in VLOOKUP is an integer that identifies the column number from which values should be returned.
But in 99% of the cases, you can leave the fourth argument out of XLOOKUP. You can still do an approximate match if needed, such as if you’re calculating tax brackets or commission rates. If you don’t specify a match type in the optional fourth argument, you’ll automatically get an exact match. In contrast, XLOOKUP and XMATCH default to an exact match. This is wrong and leads to incorrect results. When a person new to Excel leaves off the fourth argument, some of the results will be correct and thus might lead the Excel rookie to think that the False isn’t needed. “Close” is never acceptable in accounting, so 99% of the VLOOKUP scenarios require the VLOOKUP to end with False or Zero. The trouble is it defaults to the approximate match option. The optional fourth argument in VLOOKUP specifies whether you want to find an approximate or exact match. I’m a fan of VLOOKUP but concede that there are some things that make it difficult for people new to the function.
0 kommentar(er)
