Vlookup with two columns with variable columns

[michaelg]

Hi,

I tried to embed a pic of the spreadsheet, but it does not appear to work, the url is shown below.

Basically...I'm want to track my Forex paper trading.

Cells B3:M3 are the headers for the things I want to trade.

Each row below that (5 downwards) will represent a buy or sell traded.

My setup is that I enter a date in column A then;

- if I'm buying I enter a positive number in cells B:M on the same row; or
- if I'm selling I enter a negative number in the cells B:M on the same row.

these number represents the number of units I buy/sell. A running total of this units are kept in column N (eventually a running total will be created for each)

Also, when I buy, it records when I bought in column O - this repeats while the outstanding number of units is > 0

Finally, I specify whether the buy is a "long" L or "short" S purchase.

My problem is...

When I sell I need the spreadsheet to...

Check the buy date (column O), scan column A for the buy date; then

Check the same column the sell was made in the matching date.

When column A and B:M contain the buy date and a positive buy number in the same column as the sell number - get the value in column P and put it into column R on the sell row.

What I know;

MAX($B9:$M9)

- will tell me what the highest number is between B and M (only a buy or sell for 1 type of trade occurs per line)

=MATCH(MAX($B9:$M9),$B9:$M9,0)

- will tell me which column the trade occurs in.

=MATCH(O9,A:A,1)

- will match the purchase date and tell me a row (however I do not know how to match multiple similar dates).

I'm thinking a SHIFT+CTRL+ENTER formula where column A and B:M match, if they do, get value from column P.

But I'm getting a headache, any ideas?

Regards
Michael

 

[michaelg]

Hi,

Disregard this post - I've decided on an alternative approach.

Regards
Michael