Complex Lookup?

[quick7]

Hi

I need a formula to look up each specific product then find the price of the product on the latest date.

For example Row 4 so $2 for drink and row 9 $2.50 for pie.

Just an identifier on the relevant row is fine as I can then use filters.

Thanks!

[humdingaling]

can you sort the data by date first ?
vlookup will always take the first result

[Glenn Kennedy]

Here's one way to do it, using an array formula Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[quick7]

thanks so much!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.