URGENT HELP NEEDED - Index-Match with Multiple Criteria - Two Criteria with Exact Match & Third Needs Approximate Match (1 or -1)

[Nitin Gaikwad]

Hi.

I have data in two Sheets:

Sheet 1:

[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD]Cust ID[/TD]
[TD]Place[/TD]
[TD]Material[/TD]
[TD]Date of Deposit[/TD]
[TD]Deposit Qty.[/TD]
[TD]Unit Price (as on Date of Deposit)[/TD]
[TD]Date of Release [/TD]
[TD]Release Qty[/TD]
[TD]Unit Market Price (as on Date of Release) [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]aaa[/TD]
[TD]xxx[/TD]
[TD]01/01/14[/TD]
[TD]100[/TD]
[TD]1000[/TD]
[TD]23/04/14[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ddd[/TD]
[TD]xxx[/TD]
[TD]27/09/15[/TD]
[TD]50[/TD]
[TD]2300[/TD]
[TD]03/12/15[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bbb[/TD]
[TD]yyy[/TD]
[TD]14/12/14[/TD]
[TD]200[/TD]
[TD]1100[/TD]
[TD]08/05/15[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]aaa[/TD]
[TD]zzz[/TD]
[TD]30/06/15[/TD]
[TD]90[/TD]
[TD]3000[/TD]
[TD]31/12/15[/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ddd[/TD]
[TD]xxx[/TD]
[TD]02/02/15[/TD]
[TD]100[/TD]
[TD]1050[/TD]
[TD]31/03/15[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











and so on...

Sheet 2:



[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD]Place[/TD]
[TD]Material[/TD]
[TD]Date[/TD]
[TD]Unit Market Price [/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]xxx[/TD]
[TD]31/03/15[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]xxx[/TD]
[TD]01/12/15[/TD]
[TD]1050[/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]xxx[/TD]
[TD]06/12/15[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]xxx[/TD]
[TD]10/12/15[/TD]
[TD]1275[/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]yyy[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]yyy[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]zzz[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]ddd[/TD]
[TD]zzz[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]xxx[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]xxx[/TD]
[TD]..[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]xxx[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]xxx[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]






















and so on...


Now I need to search Unit Market Price from Sheet 2 (Column D) and should appear in Sheet 1 (Column I)..

I used formula


={INDEX('Sheet 2'!$D$2:$D$15000,MATCH(1,(B2='Sheet 2"!$A$2:$A$15000)*(C2='Sheet 2"!$B$2:$B$15000)*(G2='Sheet 2"!$C$2:$C$15000),0))}

Used Shift+Ctrl+Enter.

I am getting answer "if and only if" the Date of Release from Sheet 1 matches with Date in Sheet 2. e.g. 1100 for cell I6 in Sheet 1, however, in case of cell I3 in Sheet 1, I get #N/A error as the dates do not match.

Now, I want such a formula which will get me the market price from previous date / immediate earlier date (or precisely from cell D3) from Sheet 2, in the absence of matching date.


Please help. Its urgent.


Regards,

Nitin

 

[anglais428]

When you are using the MATCH function you can use a "1" to look for "less than"
e.g. Match(cell,range,1)

Putting in =INDEX(Sheet2!$D$2:$D$13,MATCH(Sheet1!G4,Sheet3!$C$2:$C$13,1))

Will give you results of 1050 for cell I3

 

[Nitin Gaikwad]

Please note that Sheet 2 is arranged in following fashion:

Place--> Material--> Date --> Unit Market Price,

where all columns are arranged as per Date in Oldest to Newest..

Hope to get reply from Excel Gurus...

 

[Nitin Gaikwad]

When you are using the MATCH function you can use a "1" to look for "less than"
e.g. Match(cell,range,1)

Putting in =INDEX(Sheet2!$D$2:$D$13,MATCH(Sheet1!G4,Sheet3!$C$2:$C$13,1))

Will give you results of 1050 for cell I3

I tried using ={INDEX('Sheet 2'!$D$2:$D$15000,MATCH(1,(B2='Sheet 2"!$A$2:$A$15000)*(C2='Sheet 2"!$B$2:$B$15000)*(G2='Sheet 2"!$C$2:$C$15000),1))} here, but I'm getting sae error...

 

[Lizard Of Confusion]

I am a newbie here was looking at your problem was following what has been said and made a sheet 2 tables on 1 sheet and some of the formulas yall mentioned i dont know enough about formulas to do much help there but i thought if all data was on one sheet for figuring out how to make formulas work would that make it easy-er then make formulas jump sheets ? i dunno
link to test sheet your info here ---> http://www.mrexcel.com/forum/test-here/918880-test2.html#post4417560

 

[Lizard Of Confusion]

Oh forgot the formulas in column Q that start with A= the A means nothing it just is keeping the formula from kicking in

I am a newbie here was looking at your problem was following what has been said and made a sheet 2 tables on 1 sheet and some of the formulas yall mentioned i dont know enough about formulas to do much help there but i thought if all data was on one sheet for figuring out how to make formulas work would that make it easy-er then make formulas jump sheets ? i dunno
link to test sheet your info here ---> http://www.mrexcel.com/forum/test-here/918880-test2.html#post4417560

 

[Marcelo Branco]

Suggestion
1. Create a helper column in Sheet1 to get the Reference Date (exact or immediate earlier date for the pair Place - Material)
Say the data in Sheet1 are in columns A:I, headers in row 1 as shown in post #1
Use column K as the helper column
K1 (header)
Reference Date

K2 (array formula)
=MAX(IF(Sheet2!$A$2:$A$15000=B2,IF(Sheet2!$B$2:$B$15000=C2,IF(Sheet2!$C$2:$C$15000<=G2,Sheet2!$C$2:$C$15000))))
Ctrl+Shift+Enter
copy down for as many lines required

2. Take advantage of the Helper column (column K) and use SUMIFS to get the Unit Market Price from the table in Sheet2
I2
=SUMIFS(Sheet2!D:D,Sheet2!A:A,B2,Sheet2!B:B,C2,Sheet2!C:C,K2)
copy down

Hope this helps

M.