## Three way Lookup

Subject Line

The THREE WAY Lookup makes multiple search criteria so simple

Overview

Sometimes you may want to do a three way lookup i.e. search on a combination of values in a table. You can build your own formula that to perform a Three -way lookup with help of INDEX and MATCH function. It can achieved with concatenating the Lookup value and use array formula to get the desired result.

Syntax

=INDEX(data,MATCH(val,rows,0),MATCH(val,columns,0))

Let’s understand both the function

INDEX function returns a value in a table based on the intersection of a row and column position within that table.

Syntax

=INDEX (array, row_num, [col_num], [area_num])

Arguments

·         array – A range of cells, or an array constant.

·         row_num – The row position in the reference or array.

·         col_num – [optional] The column position in the reference or array.

·         area_num – [optional] The range in reference that should be used.

Match option is used to locate the position of a lookup value in a row, column, or table.

Syntax

=MATCH (lookup_value, lookup_array, [match_type])

Arguments

·         lookup_value – The value to match in lookup_array.

·         lookup_array – A range of cells or an array reference.

·         match_type – [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Working

Let’s understand the working with the help of an example.

We have a report (Figure 1: Sales Report) for the year 2018.

We have to find the result based on the Sales Rep., Quarter and Criteria 3 (Country/Region/Sales) selection. Figure 1: Sales Report

Step 1: Type the below formula

=INDEX(\$A\$2:\$F\$17, MATCH(J1&J2,\$A\$2:\$A\$17&\$C\$2:\$C\$17,0), MATCH(J3,\$A\$1:\$F\$1,0))

What is Array Formula

An array formula is a formula that can perform multiple calculations on one or more items in an array.

Since this is an array formula, use it with Control + Shift + Enter, instead of just Enter.

{=INDEX(\$A\$2:\$F\$17, MATCH(J1&J2,\$A\$2:\$A\$17&\$C\$2:\$C\$17,0), MATCH(J3,\$A\$1:\$F\$1,0))}

We get the desired result Step 2: Change the criteria from the drop down list to get the desired result Understanding how the formula works with the help of Evaluate Formula

Step 1: Select the cell (J4 in this case) that you want to evaluate. Only one cell can be evaluated at a time.

Step 2: On the Formulas tab click on Evaluate Formula in Formula Auditing group Step 3: Evaluate Formula dialog box will appear. Step 4: Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.

If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula. Step 5: Click on Evaluate Step 6: Click on Evaluate Step 7: Click on Evaluate Step 8: Click on Evaluate Step 9: Click on Evaluate Step 10: Click on Evaluate Step 11: Click on Evaluate Step 12: Result Step 13: Change the Sales Rep./Quarter/Criteria 3 to get the desired results Scope of usage

ü  Can be used with drop down lists

ü  Can be used to build drop down lists

ü  Can be used to retrieve values based on a combination

ü  Can be used to VLOOKP () on two or more criteria columns

ü  Can be used with INDEX () and MATCH () function

Excel Tutorials