## 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