Index and Match functions

**bmark** · 05-01-2012, 05:32 PM

I am looking for a formula to look up and match data in 2 columns. Sheet1 contains the information. Sheet2 is where the inputs are made. In Sheet2 I want to match column A and B to the data in Sheet1 and have ColumnC in sheet2 to have the same output as the matched of columns A&B of the same row. Hopefully I have been clear enough in here and in the attachment. Thanks for your help.

**JieJenn** · 05-01-2012, 05:40 PM

in C2, enter with Ctrl + Shift + Enter altogether =INDEX(Sheet1!$C$2:$C$11,MATCH(A2&B2,Sheet1!$A$2:$A$11&Sheet1!$B$2:$B$11,0))

**Paul** · 05-01-2012, 05:43 PM

Hi Bmark,

Two options for you:

1. Use the following array formula in C2 on Sheet2. This must be confirmed using CTRL+SHIFT+ENTER, not just ENTER.

=INDEX(Sheet1!$C$2:$C$11,MATCH(A2&B2,Sheet1!$A$2:$A$11&Sheet1!$B$2:$B$11,0))

Fill that down column C as many rows as needed.

2. Use non-array formulas: Create a concatenated column on each sheet that joins the values in columns A and B. For example, on Sheet1 insert a column to the left of column A and in the new (blank) cell A2 use:

=B2&C2

Fill that down as many rows as you have data.

On Sheet2, insert a new column to the left of column A, and in the new (blank) cell A2 use:

=B2&C2

Fill that down as many rows as necessary. Then in the Number column (now column D), cell D2 use:

=VLOOKUP(A2,Sheet1!$A:$D,4,0)

This prevents you from having to use multiple array formulas.