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...

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		Oct-13			Nov-13			Dec-13
2	Item name	Cost	MRP	Qty	Cost	MRP	Qty	Cost	MRP	Qty
3	clip	20	25	3	25	30	5	18	30	6
4	stone clip	70	80	2	75	90	4	65	90	8
5	oval clip	60	72	1	60	75	0	60	80	2
6
7
8	Item	stone clip
9	Date	Nov-13
10	Column	MRP
11	Result	90
Lookup Value

 

[vijayamani1987]

It exactly matching my requirement, Thanks a lot Peter

 

[vijayamani1987]

Thanks Peter...U made it as I requested.

But i got a problem. In B9 my input is in DD-MM-YY format. The formula gives #N/A error when i give date value other than 1. ie it works well when the date is 01-03-14, but it gives error when the date is 02-03-14. Hope I explained my problem well. Again looking for your help.

 

[chandramohan_fm]

I think we can use this formula if you are looking range dates comparison. i.e., suppose we have table with MRP data for 1-OCT-13 and 1-NOV-13.if we enter 15-OCT-13 date in cell B9 then below formula retrieve MRP on 1-OCT-13.

={IFERROR(INDEX(B3:J5,MATCH(B8,A3:A5,0),MATCH(INDIRECT(ADDRESS(ROW(B1),MAX(IF(B1:J1<>"",IF(B1:J1<=B9,COLUMN(B1:J1),""),"")))),B1:J1,0)+MATCH(B10,B2:D2,0)-1),"Data not available")}

we need to press ctrl+shift+enter after entering this formula in cell B9 to get array brackets.

Sory if it is not matching with you requirement.

 

[Aladin Akyurek]

Also: Control+shift+enter, not just enter:

=IFERROR(INDEX($B$3:$J$5,MATCH("stone clip",$A$3:$A$5,0),
  MATCH(1,IF(COLUMN($B$1:$J$1)-COLUMN($B$1)+1>=
  MATCH("1-Nov-13"+0,$B$1:$J$1-DAY($B$1:$J$1)+1,0),IF($B$2:$J$2="MRP",1)),0)),"")

 

[markmzz]

Thanks Peter...U made it as I requested.

But i got a problem. In B9 my input is in DD-MM-YY format. The formula gives #N/A error when i give date value other than 1. ie it works well when the date is 01-03-14, but it gives error when the date is 02-03-14. Hope I explained my problem well. Again looking for your help.

Try this small modification in Peter's formula:

=INDEX(B3:J5,MATCH(B8,A3:A5,0),MATCH([COLOR="#FF0000"]--TEXT(B9,"mm/yy")[/COLOR],B1:J1,0)+MATCH(B10,B2:D2,0)-1)

Markmzz

 

[Peter_SSs]

Thanks Peter...U made it as I requested.

But i got a problem. In B9 my input is in DD-MM-YY format. The formula gives #N/A error when i give date value other than 1. ie it works well when the date is 01-03-14, but it gives error when the date is 02-03-14. Hope I explained my problem well. Again looking for your help.

My modification for this would be

=INDEX(B3:J5,MATCH(B8,A3:A5,0),MATCH(B9-DAY(B9)+1,B1:J1,0)+MATCH(B10,B2:D2,0)-1)

 

[vijayamani1987]

Thank you very much for all of you....it helped me a lot

 

[vijayamani1987]

Guys, in that same formula (Preferably Peter Ss or Markmzz formula) i need one more change.

	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>

If i didnt give the input value of one month, i want the formula to choose the value of previous available month ie. in this example if i search for march-14, the formula has to return to Dec-13.
Hope I gave a clear statement of the problem. Looking forward for help!!!