Lookup with criteria / Index match (?) / Using dates

Hello guys
I´m not sure which functions to combine to the desired result. I've uploaded the sheet, it is in portuguese but that won't matter much I hope, I'll explain the best I can.

What I need to do is:

On the third tab (Cobrança), column H (Valor unitario de compra), I need to do a special lookup from the second tab (Valores ENTRADA + SAIDA), bringing me the value from column O (Saldo V. Unitario).

The conditions are:

1)Matching the product from column D from the third tab with the second tab (column E)
2)Must search only from rows with the string "compra" on column B from the second tab
3)As the value searched is dynamic, the date from second tab must be the LAST, EQUAL OR PRIOR, the one from the third column.

Example:

The first data on the third tab is hashi sticks sold on 16/09. The value searched on second tab is O160, because its the unit price of the product on its last purchase, 21/08, prior to the sale's date (row 160).


This seems a complex task to me so Im not sure how to begin solving it.

If anyone else could give me an opinion about this:

Please Login or Register  to view this content.

My attempt looks something like the formula above, however it errors out and I dont know how to include my condition 2)

double post

Both formulas work to some extent, but the thing is ('Valores ENTRADA + SAÍDA'!$C$2:$C$474<=B2) makes it search for the wrong value, probably because it partially meets my needs. It shouldn´t be only an older date, it must be the last unit value of my inventory product before I make a sale. The unit value can change a lot, for example:

10/10 - Bought 10 units for $10 (I have 10 units, value of my units in stock = 10)
11/10 - Bought 5 units for $20 from another seller ( I have 15 units, value of my units in stock = 13,33)
12/10 - I resell some of these items for $30

What I actually profit is the difference 30-13,33 and not 30-10. Thats why I need it to search for the LAST DATE of purchase, currently the formula with <= is not specifying this

It seems the match formula won't allow a max or countif formula as a fourth criteria added to the ones we already have, it only compares cells, is it correct?

Still need help please

Thank you for your reply, but it doesn´t work

Also, how is the "Row Criteria" choosing the last date of purchase like I explained above, while considering also<= B2, and not only selecting one row before the last of the table?