The intersect operator is a little known way you can lookup a cell or range of cells.
Excel Lookup formulas are redundant because the intersect operator can be used to achieve the same results. The intersect operator is a one-liner formula that can be used to quickly look up data from two or more tables. It is much easier to use and more efficient than using multiple lookup formulas. Additionally, the intersect operator is more versatile, allowing for multiple criteria to be specified and for more complex calculations.
So we are asking Excel to fetch the value where the two ranges i.e. first range pulled by INDIRECT(B9) which is of Month and second range pulled by INDIRECT(B ...
Mar 14, 2023 — It can look up both vertically and horizontally and perform an exact match (default), approximate (closest) match, or wildcard (partial) match.
Doesn’t include: REDUNDANT! | Must include: REDUNDANT!
Using a formula to create a unique list from a larger list of duplicates.
Problem: The lookup value is not in the first column in the table_array argument · Consider using INDEX/MATCH instead · Problem: The exact match is not found.
7 key moments in this video
Xlookup Syntax · Xlookup Examples – Table · Got Xlookup Yet? Share Your...
More Excel Formulas Posts · Reader Interactions · Sidebar