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]

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

I don't quite understand that as the month before March 14 is February 14 not December 13.
Anyway, the change shown below to my previous formula will draw the result from whatever the section immediately to the left of the lookup date is. Of course then you wouldn't be able to use the very first month in the table as the search month.

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)-4)

 

[vijayamani1987]

Sorry for the less clearance in my statement Peter_SSs. Here I am repeating.

	A	B	C	D	E	F	G	H	I	J
1		Oct-13	Nov-13	Mar-14
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

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 92px;"><col style="width: 74px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"></colgroup><tbody>
</tbody>

In row-1, I may not use all the months. In this example jumped from Nov-13 to Mar-14.
So when I am looking for a particular month, if that month (& year) is not listed in row-1, I want the values of the previous available month.

For example if I am searching for Feb-14, it is not listed there. So I want to select the previous available month ie. Nov-13.
If I am searching for Mar-14, it has to select the Mar-14.

Hope I made a clear statement. Please give me a solution.

 

[Aladin Akyurek]

Sorry for the less clearance in my statement Peter_SSs. Here I am repeating.

	A	B	C	D	E	F	G	H	I	J
1		Oct-13	Nov-13	Mar-14
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

<tbody>
</tbody>

In row-1, I may not use all the months. In this example jumped from Nov-13 to Mar-14.
So when I am looking for a particular month, if that month (& year) is not listed in row-1, I want the values of the previous available month.

For example if I am searching for Feb-14, it is not listed there. So I want to select the previous available month ie. Nov-13.
If I am searching for Mar-14, it has to select the Mar-14.

Hope I made a clear statement. Please give me a solution.

You dismissed the array formula....
Nevertheless, that tweaked only very slightly for the new requirements:

	Oct-13	Nov-13	Mar-14
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
Item	stone clip
Date	Feb-14
Column	MRP
Result	90

<tbody>
</tbody>

B11, control+shift+enter, not just enter:

=IFERROR(INDEX($B$3:$J$5,MATCH(A4,$A$3:$A$5,0),
  MATCH(1,IF(COLUMN($B$1:$J$1)-COLUMN($B$1)+1>=
  MATCH(B9,$B$1:$J$1-DAY($B$1:$J$1)+1,1),IF($B$2:$J$2=B10,1)),0)),"")

 

[markmzz]

Sorry for the less clearance in my statement Peter_SSs. Here I am repeating.

	A	B	C	D	E	F	G	H	I	J
1		Oct-13	Nov-13	Mar-14
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

<tbody>
</tbody>

In row-1, I may not use all the months. In this example jumped from Nov-13 to Mar-14.
So when I am looking for a particular month, if that month (& year) is not listed in row-1, I want the values of the previous available month.

For example if I am searching for Feb-14, it is not listed there. So I want to select the previous available month ie. Nov-13.
If I am searching for Mar-14, it has to select the Mar-14.

Hope I made a clear statement. Please give me a solution.

Hi Vijayamani1987,

If I understand correctly what you want, maybe this can helps:

=INDEX(B3:J5,MATCH(B8,A3:A5,0),MATCH(--TEXT(B9,"mm/aa"),B1:J1)+MATCH(B10,B2:D2,0)-1)

Markmzz

 

[Peter_SSs]

Does this small tweak to my original non-array formula from post #2 do what you want?

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

Note that it is a slightly different interpretation of your requirement to Aladin.


@Markmzz
Did you really mean TEXT(B9,"mm/aa") ?

 

[markmzz]

Hi Vijayamani1987,

If I understand correctly what you want, maybe this can helps:

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

Markmzz

Hi Vijayamani1987,

A small modification in my formula (for english version - Thanks Peter)

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

Markmzz

 

[Peter_SSs]

(for english version - Thanks Peter)

Ah, I was wondering where you got the "aa" from.
What language version do you use?

 

[markmzz]

Ah, I was wondering where you got the "aa" from.
What language version do you use?

My default language is Portuguese. Here is the original formula:

=ÍNDICE(B3:J5;CORRESP(B8;A3:A5;0);CORRESP(--TEXTO(B9;"mm/[COLOR="#0000FF"][B]aa[/B][/COLOR]");B1:J1)+CORRESP(B10;B2:D2;0)-1)

Markmzz

 

[Peter_SSs]

My default language is Portuguese.

So, do you normally just manually translate your suggested formulas to their English equivalents?

 

[vijayamani1987]

Thank you everyone of you...wil come soon with new problem

***Really a very useful forum***