Conditional Lookups

[Whirlpool]

Hi All,

Long time lurker first time poster.

I am having a little trouble with an array formula i constructed.

"{=INDEX('Data'!$S$7:$S$3050,MATCH(MIN(ABS(IF('Data'!$R$7:$R$3050=E5,'Data'!$Q$7:$Q$3050,999999)-C5)),ABS(IF('Data'!$R$7:$R$3050=E5,'Data'!$Q$7:$Q$3050,999999)-C5),0))}"

Basically the range S in 'Data' is the data its pulls, range R in 'Data' is the unique identifier code and range Q in 'Data' is the date. Subsequently range E is the unique identifier being looked up and range C is the date being looked up.

What the array currently does is match the unique identifier and pull the value closest to a date.

What I am struggling to incorporate is a condition that it cant pull the data before a date. Thus it should only pull the data which is closest to the date it matches that is after the date in range C.


Thanks for you help guys.

[DGagnon]

Firstly, welcome to the speaking/posting world

Secondly for your question, this can be done, you just need to add in an AND conditoin to the IF portion of your formula, if you post a book it would be alot easier for me to give you the corrected formula. it is difficult to do without the layout and some sample data.

[Whirlpool]

Hi DGagnon,

I thought that would be the case im just having trouble slotting it in.

Please see attached.

Thanks

[DGagnon]

give this formula a shot, its a variation of what you had, but kind of dumbed down a bit, but based on what you were looking for it should work.

=INDEX(Data!$S:$S,MAX(--(Data!R$7:R$3050=E4)*--(Data!Q$7:Q$3050<=C4)*ROW(Data!Q$7:Q$3050)))

[Whirlpool]

give this formula a shot, its a variation of what you had, but kind of dumbed down a bit, but based on what you were looking for it should work.

=INDEX(Data!$S:$S,MAX(--(Data!R$7:R$3050=E4)*--(Data!Q$7:Q$3050<=C4)*ROW(Data!Q$7:Q$3050)))

Hi Dgagnon thanks for your help,

Problem is my source database sometimes has anomalies in the data where the data in sheet 1 has a subsequent date earlier than the data sheet. Thus the functionality I was looking to add in was to only pull the later dates data if there was one I have attached the workbook again for further information.

Basically I want the formula to have the logic of where a match can be found with the code after the date pull that data, however, if no match can be found after that date pull the closest before for that match.


Thanks for your help once again.

Cheers

[JBeaucaire]

Try this regular formula in J4, then copy down:

=IFERROR(LOOKUP(2, 1/((Data!$Q$7:$Q$20<=C4)*(Data!$R$7:$R$20=Sheet1!E4)), Data!$S$7:$S$20), INDEX(Data!S:S, MATCH(E4,Data!R:R, 0)))

[Whirlpool]

Thanks for all your help guys i ended up with a variation of the formulas you guys helped me with...

=IFERROR(LOOKUP(2,1/(('Data'!$Q$7:$Q$3055<=C5)*('Data'!$R$7:$R$3055=E5)),'Data'!$S$7:$S$3055),INDEX('Data'!$S$7:$S$3055,MATCH(MIN(ABS(IF('Data'!$R$7:$R$3055=E5,'Data'!$Q$7:$Q$3055,999999)-C5)),ABS(IF('Data'!$R$7:$R$3055=E5,'Data'!$Q$7:$Q$3055,999999)-C5),0)))

Basically kept my original array formula but changed the front end....