Help please with vlookup or index match

[mina91709]

I AM TRYING TO USE VLOOK UP OR ANY OTHER FORMULA THAT WOULD WORK WITH MY APPROACH.

FORMULA WILL LOOK UP THE DATE FROM MULTIPLE TABLES AND POPULATE 1ST AND 2ND VALUES FOR THE CATEGORiES BASED THE DATE VALIDATION NOTE IT WILL GO TO THE NEXT VALID DATE SEE EXAMPLE BELOW I AM SORRY IF I AM NOT CLEAR BUT WHAT I AM TRYING TO DO IS INPUT THE DATE IN THE DATE INPUT AND IT WILL SEARCH THE TABLES BELOW TO POPULATE 1ST VALUE AND 2ND VALUE. FROM THE EXAMPLE BELOW I PUT IT 7/15/2008 SO IT LOOKED UP THE TABLES AT THE BOTTOM AND PICKED UP THE VALUES VALID FOR THAT DATE SO IT PICKUP THE VALUES FOR 12/31/2008
THANK YOU IN ADVANCE

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]DATE INPUT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]7/15/2008[/TD]
[/TR]
</tbody>[/TABLE]


GIVEN TABLES/LOOKUP TABLE
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD][/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]VALID TO DATE[/TD]
[TD][/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]VALID TO DATE[/TD]
[TD][/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]80[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]60[/TD]
[TD]5[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]120[/TD]
[TD]80[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]8[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]6/30/2009[/TD]
[/TR]
</tbody>[/TABLE]

 

[AliGW]

Please take a moment to re-write your post in normal sentence case - the all capitals text is really hard to read. Then please explain how the two tables relate to each other - I can see no matching features.

 

[Special-K99]

1) Turn your CAPS LOCK off.
2) Try

in Sheet 1!B2
=INDEX(Sheet2!A$2:M$5,MATCH(Sheet1!A2,Sheet2!A$2:A$6,0),IF(Sheet1!D$2<=Sheet2!E$2:E$6,C$2:C$6,IF(Sheet1!D$2<=I$2:I$6,G$2:G$6,K$2:K$6)))
Array formula, use Ctrl-Shift-Enter
and copy down the column

in Sheet 1!C2
=INDEX(Sheet2!A$2:M$5,MATCH(Sheet1!A2,Sheet2!A$2:A$6,0),IF(Sheet1!D$2<=Sheet2!E$2:E$6,D$2:D$6,IF(Sheet1!D$2<=I$2:I$6,H$2:H$6,L$2:L$6)))
Array formula, use Ctrl-Shift-Enter
and copy down the column

 

[63falcondude]

1) Turn your CAPS LOCK off.
2) Try

in Sheet 1!B2
=INDEX(Sheet2!A$2:M$5,MATCH(Sheet1!A2,Sheet2!A$2:A$6,0),IF(Sheet1!D$2<=Sheet2!E$2:E$6,C$2:C$6,IF(Sheet1!D$2<=I$2:I$6,G$2:G$6,K$2:K$6)))
Array formula, use Ctrl-Shift-Enter
and copy down the column

in Sheet 1!C2
=INDEX(Sheet2!A$2:M$5,MATCH(Sheet1!A2,Sheet2!A$2:A$6,0),IF(Sheet1!D$2<=Sheet2!E$2:E$6,D$2:D$6,IF(Sheet1!D$2<=I$2:I$6,H$2:H$6,L$2:L$6)))
Array formula, use Ctrl-Shift-Enter
and copy down the column

Nice! I had to make a few changes to make this work for me.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	CATEGORY		1ST VALUE	2ND VALUE	VALID TO DATE		1ST VALUE	2ND VALUE	VALID TO DATE		1ST VALUE	2ND VALUE	DATE
2	A		15	20	6/30/2008		10	10	12/31/2008		30	80	6/30/2009
3	B		60	5	6/30/2008		20	20	12/31/2008		100	50	6/30/2009
4	C		5	8	6/30/2008		30	30	12/31/2008		120	80	6/30/2009
5	D		7	9	6/30/2008		40	40	12/31/2008		50	100	6/30/2009
6	E		200	8	6/30/2008		50	50	12/31/2008		100	50	6/30/2009
Sheet2

Excel 2010
	A	B	C	D
1	CATEGORY	1ST VALUE	2ND VALUE	DATE INPUT
2	A	10	10	7/15/2008
3	B	20	20	7/15/2008
4	C	30	30	7/15/2008
5	D	40	40	7/15/2008
6	E	50	50	7/15/2008
Sheet1

Cell Formulas
Range	Formula
B2	{=INDEX(Sheet2!A$2:M$6,MATCH(A2,Sheet2!A$2:A$6,0),IF(D$2<=Sheet2!E$2:E$6,3,IF(D$2<=Sheet2!I$2:I$6,7,11)))}
C2	{=INDEX(Sheet2!A$2:M$6,MATCH(A2,Sheet2!A$2:A$6,0),IF(D$2<=Sheet2!E$2:E$6,4,IF(D$2<=Sheet2!I$2:I$6,8,12)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mina91709]

thank you all for the help.
The tables are on the same sheet. Is there a formula that will work within the same sheet.

Thank you

 

[63falcondude]

thank you all for the help.
The tables are on the same sheet. Is there a formula that will work within the same sheet.

Thank you

Same formulas just adjust the references and get rid of the "Sheet2!". I'm sure that you can figure that out looking at the layout in post #4.

 

[dUBBINS]

Same formulas just adjust the references and get rid of the "Sheet2!". I'm sure that you can figure that out looking at the layout in post #4.

Perhaps Six-Three, but they asked, so might as well answer. Never know what the other person may or may not have knowledge of; and it may educate someone else that stumbles upon this page one day. A lot of times, I find an answer somewhere, but the person answering is so knowledgeable that they forget to explain a lot of the "little" things that really make it all work. The more you know (enter star graphic here).

Here is the adjustment to Falcon's formulas:

{=INDEX(A$2:M$6,MATCH(A2,A$2:A$6,0),IF(D$2<=E$2:E$6,3,IF(D$2<=I$2:I$6,7,11)))}
{=INDEX(A$2:M$6,MATCH(A2,A$2:A$6,0),IF(D$2<=E$2:E$6,4,IF(D$2<=I$2:I$6,8,12)))}

And again...

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[mina91709]

Thank you everyone for your help. I will try it out and will let you know.

Perhaps Six-Three, but they asked, so might as well answer. Never know what the other person may or may not have knowledge of; and it may educate someone else that stumbles upon this page one day. A lot of times, I find an answer somewhere, but the person answering is so knowledgeable that they forget to explain a lot of the "little" things that really make it all work. The more you know (enter star graphic here).

Here is the adjustment to Falcon's formulas:

{=INDEX(A$2:M$6,MATCH(A2,A$2:A$6,0),IF(D$2<=E$2:E$6,3,IF(D$2<=I$2:I$6,7,11)))}
{=INDEX(A$2:M$6,MATCH(A2,A$2:A$6,0),IF(D$2<=E$2:E$6,4,IF(D$2<=I$2:I$6,8,12)))}

And again...

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself