Help and in the contents or index area, type in "keyboard", from a Keyboard shortcuts and keyboard help is available in many "Help"įiles of Microsoft products. The INDIRECT function returns the range for the named ranges – Sales rep and the Month, and the space between them works as an intersect operator and returns the intersecting value.Shortcut Keys in Excel 2000 through Excel 2007 Applies to ALL versions of Excel though started from Excel 2000 (Excel Vers. Notice that there is a space in between the two INDIRECT formulas. Now in cell C16, use the following formula =INDIRECT(B16) INDIRECT(C15).Similarly, go to cell C15 and create a drop down list for all the months.Now go to cell B16 and create a drop-down list for all the sales rep.This will create named ranges for all the Sales Reps and all the Month.Select the ‘Top Row’ and ‘Left Column’ options and click OK.This will open a ‘Create Names from Selection’ dialogue box.
Select the entire data set (B3:N13) and press Control + Shift + F3 to create named ranges (it can also be done through Formula –> Defined Names –> Create from Selection).I have also created a drop-down list with Sales Rep Name in one cell and Month name in another, and I want to extract the sales that the Rep did in that month. I have a data-set of Sales Rep and the sales they made in each month in 2012. Here is a situation where this trick might come in handy. Similarly, you can use =Prdt1:Prdt2 Apr to get the intersection of Product 1, Product 2 and April.Ī Practical Example of Using Intersect Operator in Excel Now you can use the formula =Prdt1 Apr to get the intersection of these 2 ranges. Here is an example where I have named the Product 1 values as Prdt1, Product 2 values as Prdt2 and April Values as Apr. You can also use named ranges to find the intersection using the Intersect Operator in Excel. You can use this within formulas, such as SUM (to get the total of the intersection values) or MAX (to get the maximum of the intersection values). This formula returns an array of the intersection values. Note that the result of this formula would display a Value error, however, when you select the formula and press F9, it will show the result as . Here is the formula that can do that: =B2:C13 B5:D5 For example, with the same data set as shown above, you can get the intersection of Product 1 and Product 2 in April. You can also use the same technique to find the intersection of ranges that spans more than one row or column. Intersection of a Multiple Rows and Columns Now if you use =C2:C13 B5:D5, it will return 523 (the value in cell C5), which is the intersection of these 2 ranges. Suppose there is a data set as shown below: The intersection of multiple rows and columns.The intersection of a single row and column.You can use Intersect Operator in Excel to find: If you use a space character in between two ranges, then it becomes the Intersect operator in Excel. This an unusual operator as it is represented by a space character (yes that’s right). Intersect Operator in Excel can be used to find the intersecting value(s) of two lists/ranges. Watch Video – Using Intersect Operator in Excel
0 kommentar(er)
