Lookup the date and return the latest value under some conditions from multiple columns

[PMH2384]

Hello, Someone can help me please ?

I am trying to work around, Sumifs,Vlookup, Index , Match, Offset , etc.. But, I couldn't figure out how to combine all these together.

I want to get the price by looking up "invoice date" and matching "client code" and "item code" and "Quotation date" from the price list table.
They are in the different columns . Prices are varies for one customer . Eg , 1/1/2016 price is 15 , then 1/5/2016 price is $18 . Then latest price as at 1/8/2016 is $20.

I want to get price by looking up related date . If invoice date is 1/7/2016 , then show price is $18 . If 5/9/2016 , then show $20.

Attached is simple file.

Thanks in advance.

[FlameRetired]

Try array-entering this formula in M3 and filling down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[PMH2384]

Thank you very much, Dave. This solved my problem exactly.
Appreciate for your help ..

[FlameRetired]

You are welcome. Thank you for the feedback.

[PMH2384]

Hello,

I would like to ask a favor again . Formula worked perfectly if price are increased.
But if later date is lower than earlier price , which formula should I use?

Current formula would show the max value only , not the latest value.

Sorry, for my missing information as I just found out there is a case like this.

[PMH2384]

Hello,

I would like to ask a favor again . Formula worked perfectly if price are increased.
But if later date is lower than earlier price , which formula should I use?

Current formula would show the max value only , not the latest value.

Sorry, for my missing information as I just found out there is a case like this.

[FlameRetired]

PMH2384,

I'm stumped.

I'll keep working on this one.

Maybe something simple I'm overlooking.

[humdingaling]

here is how i did it....not sure its the most effective way of doing it...basically brute force matching

used a helper column O just to shorten the formula down a bit you can combine it but the formula becomes a mammoth cause you refer to the helper so often

CSE formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Please Login or Register  to view this content.

then in column P

Please Login or Register  to view this content.

[PMH2384]

Hi Dave,

This is amazing formula and it worked.
I am really grateful your help and sincerely thank you for your precious time.

[FlameRetired]

Hi,

I believe that's humdingaling's formula.

Dave

[PMH2384]

I am so sorry that I overlooked . I thought you are the one who answer my another request.
How careless am I .

Dear Humdingalings ,

My apologizes.. I don't know how to say sorry to you and thanks to you.

In sincere appreciation of your valuable help, Please accept my respect and gratitude. Thank you