Index & Match Not Working

[vaibhavmodia]

Hi,

I am using Index & Match regularly. But particular in this file It is not working for both 155.11 & 155.10 (as per attached file in sheet1). When you delete data of 155.11 (as per attached file in sheet2) you will get result. But when you combine both date I can't get the result. Please tell me where i was wrong.

It is for my regular use and I have to find the solution. Please help me.

 

[Nebu]

Hi:

Use the following formula
=INDEX($J$4:$J$32,MATCH(1,MMULT(($B$4=$I$4:$I$32)*($B$5=$K$4:$K$32),1),0))

Thanks

 

[Naresh Yadav]

you can also try..

=LOOKUP(2,1/(($I$4:$I$32=B4)*(K4:K32=B5)),J4:J32)
regards
Naresh

 

[bosco_yip]

1] Your formula in B10 :

{=INDEX(J4:J32,MATCH(B4&B5,I4:I32&K4:K32,0))}

Whereas,

B4 =155.1

B5 =17

and, the matching criteria,

=B4&B5

=155.1&17

=155.117

It became one number, not two numbers, thus the formula failure.

2] Therefore,

The formula should be changed to :

{=INDEX(J4:J32,MATCH(B4&"|"&B5,I4:I32&"|"&K4:K32,0))}

In adding a "|" seperator to separate two numbers.

Regards
Bosco

 

[vaibhavmodia]

Thanks all...It really help me & Thanks Bosco, I also want to know that why it happens and you describe me well.

Thanks again all.