CODEWITHSUNDEEP
excelindexingexcel-formula
Kunal Sharma
Kunal Sharma

Reputation: 107

Excel index and match formula not working as expected

I am trying to use Index and match function of the excel to get the value of a column

I have two tabs in excel as below:

tab1
    Component      Complexity       Value
    C1               Hard          
    C2               Soft

tab2
    Component     Hard      Soft
    C1             10        20
    C2             5         10

I have used Index and match to populate the value in tab1 sheet by looking up "component" and "Hard/soft" value in Tab2 sheet using this formular INDEX(tab2!B2:C3,MATCH(1,(A2=tab2!A2:A3)*(B2=tab2!B1:c1),0)) to get 10 and 10 under the value column in tab1 sheet

However the above formula is returning N/A value and the expected values, Can someone let me know what I am doing wrong in the formula

Upvotes: 0

Views: 92

Answers (3)

Kairu
Kairu

Reputation: 373

You can use Index/Match/Match to also include the column lookup

=INDEX('tab2'!$A$1:$C$3,MATCH('tab1'!$A2,'tab2'!$A$1:$A$3,0),MATCH('tab1'!$B2,'tab2'!$A$1:$C$1,0))
  • The first parameter in the INDEX formula 'tab2'!$A$1:$C$3 sets the array where it needs to look up the row and the column.
  • The second parameter in the INDEX, being the first MATCH formula looks for the Component row
  • The third parameter in the INDEX, being the second MATCH formula looks for the Complexity column that was set in the first parameter

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

You got many options. I would indeed use INDEX and MATCH as proposed by @JvdV in his answer, but you could do this too:

enter image description here

Formula in cell C2:

=SUMIF($A$7:$A$8,A2,CHOOSE(MATCH(B2,$B$2:$B$3,0),$B$7:$B$8,$C$7:$C$8))

Formula in cell H2:

=SUMPRODUCT(($A$7:$A$8=F2)*$B$7:$C$8*($B$6:$C$6=G2))

Upvotes: 3

JvdV
JvdV

Reputation: 75840

Assuming a version of Excel without dynamic arrays, try:

enter image description here

Formula in C2:

=INDEX($F$2:$G$3,MATCH(A2,E$2:E$3,0),MATCH(B2,F$1:G$1,0))

Note the importance of semi-absolute referencing here!

A little less verbose, and possible because of numeric input:

=SUMIF(E$2:E$3,A2,INDEX(F$2:G$3,,MATCH(B2,F$1:G$1,0)))

Upvotes: 5

Related Questions