CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
kevin c
kevin c

Reputation: 805

Excel INDEX and MATCH Get Value

I have an excel workbook that I need some help with INDEX and MATCH or any other Formula that can get me my end result.

Here is sheet1:

SIT_ID      METER   SUSE_CD
10834282    DT0061      B 
10834282    AW7931      P 
21676286    CQ9635      P 
21676286    DP4838      B 
21726281    AW7880      P 
21726281    DT0032      B

Here is Sheet2:

Site ID        B    P
10834282        
21676286        
21726281

Ultimately what I am trying to do is on Sheet2 is put the Meter that = B for the SITEID in the column and then Put the Meter that = P in the Same row.

I have never used Index or Match and I looked it up online but I am confused and hoping someone can help me with the correct formula or point me in the right direction.

Thanks so much!

Upvotes: 2

Views: 1925

Answers (4)

Vickar
Vickar

Reputation: 953

The easiest way to get around with this is, to use concatenation operator in the match function.

Don't forget to use Ctrl+Shift+Enter

Use below formula in column B of Sheet 2

{=INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2&Sheet2!$B$1,Sheet1!$A:$A&Sheet1!$C:$C,0))}

And the below formula in column C of Sheet 2

{=INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2&Sheet2!$C$1,Sheet1!$A:$A&Sheet1!$C:$C,0))}

And then flash fill the remaining rows.

enter image description here

Upvotes: 0

Jerry
Jerry

Reputation: 71598

INDEX first takes a range, then a row number, an optional column number (and an optional area number).

MATCH takes a value to lookup, an array and a mode.

In your problem you can use the following in Sheet2 cell B2:

=INDEX(Sheet1!$B$2:$B$7, MATCH($A2, IF(Sheet1!$C$2:$C$7=B$1,Sheet1!$A$2:$A$7), 0))

This formula is an array formula and will work with Ctrl+Shift+Enter and then you can fill it to the other cells.

I had to use an IF because there're two conditions to check.

EDIT: Use this one if your cell formats are different:

=INDEX(Sheet1!$B$2:$B$7,MATCH($A2*1,IF(Sheet1!$C$2:$C$7=B$1,Sheet1!$A$2:$A$7*1),0))

EDIT2: Adding trimming:

=INDEX(Sheet1!$B$2:$B$7,MATCH($A2*1,IF(TRIM(Sheet1!$C$2:$C$7)=TRIM(B$1),Sheet1!$A$2:$A$7*1),0))

EDIT3: If you're using it on your full data, change the range:

=INDEX(Sheet1!$B:$B,MATCH($A2*1,IF(TRIM(Sheet1!$C:$C)=TRIM(B$1),Sheet1!$A:$A*1),0))

Upvotes: 4

Jüri Ruut
Jüri Ruut

Reputation: 2530

A helper column D is added to initial columns.
D2: =$A2 & $C2

Now it's possible to make a simple search of the concatenated SITE_ID and SUSE_CD:
H2: =MATCH($G2&" B";$D$2:$D$8;0)

The result would be a row number (=1 in this case) for the needed string in array $D$2:$D$8.

INDEX shows the value of the cell, found by counting n-th row (defined by MATCH) and m-th column (=2) in array $A2:$A$8 from the upper left cell (A2).

Altogether: =INDEX($A$2:$B$8;MATCH($G2&" B";$D$2:$D$8;0);2)

enter image description here

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26670

Assuming your Sheet1 looks like this:

tigeravatar Sheet1 data setup

And your Sheet2 looks like this:

tigeravatar Sheet2 data setup

The formula in Sheet2 cell B2 and copied over and down to cell C4 is:

=INDEX(Sheet1!$B$2:$B$7,MATCH(1,INDEX((Sheet1!$A$2:$A$7=$A2)*(Sheet1!$C$2:$C$7=B$1),),0))

Note that this is a regular formula, so no need for Ctrl+Shift+Enter

Upvotes: 1

Related Questions