spot_img

Two-way Lookup

 

To lookup a value in a two-dimensional range, use INDEX and MATCH in Excel. Below you can find the sales of different ice cream flavors in each month.

1. To find the position of Feb in the range A2:A13, use the MATCH function. The result is 2.

Match Function

2. To find the position of Chocolate in the range B1:D1, use the MATCH function. The result is 1.

Match Function

3. Use these results and the INDEX function to find the sales of Chocolate in February.

Index Function

Explanation: 217 found at the intersection of row 2 and column 1 in the range B2:D13.

4. Put it all together.

Two-way Lookup in Excel

You can also lookup a value in a two-dimensional range without using INDEX and MATCH. The following trick is pretty awesome.

5. Select the range A1:D13.

Select Range

6. On the Formulas tab, in the Defined Names group, click Create from Selection.

Create Named Ranges

7. Check Top row and Left column and click OK.

Create Names from Selection

8. Excel created 12 + 3 = 15 named ranges! Simply select a range and look at the Name box.

Named Range

9. Use the intersect operator (space) to return the intersection of two named ranges.

Intersection of Named Ranges

10. Create a dynamic two-way lookup.

Dynamic Two-way Lookup

Explanation: the INDIRECT functions convert the text strings (“Feb” in cell G2 and “Chocolate” in cell G3) into valid named ranges.

spot_img
Previous articleTable Styles in Excel
Next articleCell References in Excel