Microsoft Excel home and student 2010 so...

Hello-

I am stumped. I have a Table A of stock transactions. Col 1=date; Col 2=# of shares; Col 3 = "shares are"; Col 4 = either "purchased" or "sold. I then have a Table B of stock price data. Col 1 = date; Col 2 = stock price; Col 3 = a formula to be (#shares x share price) - positive for a sale, negative for a purchase.I am to compare my date in Table A, Col 1 with Table B, Col 1. If there is a match, I calculate the transaction total = # shares x share price, negative for a purchase, positive for a sale. If no date match is found, display a zero.

I have been trying to construct a nested If statement and vlookup combination to look at the "sold" or "purchased" column to determine the proper sign. HELP !!!!!!

You'll have to modify this to fit the ranges that your tables use, but the concept should be valid. I've broken it down into 4 lines to make it clearer.

Let's assume:

Table A is in A1:D2

Table B is in F1:G1

F1 contains the date you are looking up

G1 contains the Stock price- The first part will return 0 if the date in F1 isn't found in Table A Column 1.

- If the date is found, the second part will check for "Sold" in Column 4 of Table A

- If Sold is there, the third part will VLOOKUP the # of Shares and multiply that value by the Share Price in G1.

- If Sold is not there, the fourth part will VLOOKUP the # of Shares and multiply that value by thenegativeof the Share Price in G1.

=IF(ISNA(VLOOKUP(F1,$A$1:$D$2,1,0)), 0,

IF(VLOOKUP(F1,$A$1:$D$2,4,0)="Sold",

VLOOKUP(F1,$A$1:$D$2,2,0)*G1,

(VLOOKUP(F1,$A$1:$D$2,2,0)*-G1)))

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

I modified your formula to fit my spreadsheet. See below. Still doesn't work. The displayed result is 0.00

=IF(ISNA(VLOOKUP(A303,$O$11:$R$20,1,TRUE)),0,

IF(VLOOKUP(A303,$O$11:$R$20,4,FALSE)="sold",

VLOOKUP(A303,$O$11:$R$20,2,FALSE)*G1,(VLOOKUP(A303,$O$11:$R$20,2,FALSE)*-B303)))

I got it to work..Here we go. Thanks for your help.

=IF(ISNA(VLOOKUP(A288,$O$11:$R$20,1,FALSE))=TRUE,0,

IF(VLOOKUP(A288,$O$11:$R$20,4,FALSE)="sold",

VLOOKUP(A288,$O$11:$R$20,2,FALSE)*B288,(VLOOKUP(A288,$O$11:$R$20,2,FALSE)*-B288)))

I was going to ask you why you had TRUE in here, but you fixed it already. ISNA(VLOOKUP(A303,$O$11:$R$20,1,TRUE))

That said, you don't need the =TRUE here:

IF(ISNA(VLOOKUP(A288,$O$11:$R$20,1,FALSE))=TRUE,

It's redundant.

Finally, you can save keystrokes by using 0 (or 1) instead of FALSE (or TRUE) in your VLOOKUPS.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Thanks.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History