CODEWITHSUNDEEP
google-sheetsarray-formulasgs-vlookup
ScaZ
ScaZ

Reputation: 41

Conditional VLOOKUP with unique match by column

I want to do the following: enter image description here

The BUY and SELL area is done and ordered by date descending, but I need help to calculate the profit of each article.

What I need is to have the PROFIT Area filled like in the example.

My idea is doing it with a VLOOKUP, but I need it to use only one Transaction ID each time (one for BUY, other from SELL).

EDIT: I want all the BUY Transaction ID on "Column I" and the nearest SELL Transaction ID on his side on "Column J". If there's isn't a SELL Transaction, put only the BUY Transaction ID and leave the SELL Transaction ID in BLANK.

I hope I made myself clear.

Thanks in advance!

Upvotes: 0

Views: 398

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

Profit is calculated only when you sell smth, so I suggest to continue the second table SELL:

enter image description here

If we use FIFO: first in → first out, then we want to get first apple from BUY table. To get this use index:

=INDEX(FILTER({$A$3:$A,$C$3:$C},$B$3:$B=F3),COUNTIF(F$2:F3,F3))

Drag this formula down. Then use =G3-I3 for profit.

For your regional settings please try:

=INDEX(FILTER({$A$3:$A\$C$3:$C};$B$3:$B=F3);COUNTIF(F$2:F3;F‌​3))

Edit

As @anonymous mentioned, if you need first last value (earliest), the formula is to be sorted by row number since you have no dates:

=INDEX(QUERY(FILTER({row($A$3:$A)\$A$3:$A\$C$3:$C};$B$3:$B=F3);"select Col2, Col3 order by Col1 desc");COUNTIF(F$2:F3,F3))

Upvotes: 1

Related Questions