Three way lookup (1 row, two columns)

[vijayamani1987]

Here is my requirement...

	Oct-13	Nov-13	Dec-13
Item name	Cost	MRP	Qty	Cost	MRP	Qty	Cost	MRP	Qty
clip	20	25	3	25	30	5	18	30	6
stone clip	70	80	2	75	90	4	65	90	8
oval clip	60	72	1	60	75	0	60	80	2

<tbody>
</tbody>

I need to find the MRP value of stone clip for nov-13.

I am not much familiar with excel. Please give me a solution and thanks a lot in advance...

 

[Aladin Akyurek]

D8:

=SUMPRODUCT(
  --(Master!$E$12:$E$100=$C8),
  --(TEXT(Master!$B$12:$B$100,"mmmyy")=TEXT(E$5,"mmmyy")),
  Master!$F$12:$F$100,
  Master!$G$12:$G$100)

Also:

=SUMPRODUCT(
  --(Master!$E$12:$E$100=$C8),
  --(Master!$B$12:$B$100-DAY(Master!$B$12:$B$100)+1=E$5),
  Master!$F$12:$F$100,
  Master!$G$12:$G$100)

...

See blue colored cells...

https://dl.dropboxusercontent.com/u/65698317/mrexcel1%20vijayamani1987.xlsm

 

[vijayamani1987]

Thank u

 

[Aladin Akyurek]

Thank u

At last... You are welcome.

 

[markmzz]

,

Hi Vijayamani1987,

Did you try my formula of the post #38?

If yes, what you got?

Markmzz

 

[vijayamani1987]

Hi markmzz,

That formula worked well. And got the expected value #60.
Thank u so much.

 

[markmzz]

Hi markmzz,

That formula worked well. And got the expected value #60.
Thank u so much.

I'm glad to hear that my formula worked and you are welcome.

Markmzz