Excel Column and Row Lookup via Index and Match

Example

Let's start with two simple tables. The first contains the engagement budget. The second contains project budgets. We'd like to get the engagement budget included in the second table. 

Engagement Budget Table

Project Budget Table

Where you see the question marks, we are going to insert a formula. The formula will take the engagement code from the second table, look for it the first table, and then return the budget.

Here are the formulas we will use:

• INDEX(array, row, column)
• MATCH(value, array, match type)
• COLUMN(name)

Let's start with the first parameter of INDEX. Which table contains the data we need? The first table. Enter the table name.

• =INDEX([Engagement Budget Table], row, column)

The second parameter of INDEX is which row of data contains the value we need. For example, In the first table, I need row two when looking at engagement P001002. This is where the magic of MATCH happens. MATCH searches the Engagement Code column.

• =INDEX([Engagement Budget Table], MATCH([@[Engagement Code]], [Engagement Budget Table], 0), column)

The third parameter of INDEX is the target column, engagement budget.

• =INDEX([Engagement Budget Table], MATCH([@[Engagement Code]], [Engagement Budget Table], 0), COLUMN(Engagement Budget Table[Engagement Budget]))

So that is a complicated formula, but "reading" it left to right, you would say to yourself - In the Engagement Budget Table, find my engagement code in the engagement code column. Then go to the Budget column and get the value.

The end result table would look like this:

Project Budget Table (end result)