Multiple (nested) VLOOKUP functions

Microsoft Excel home and student 2010 so...
January 31, 2011 at 08:55:25
Specs: Windows 7
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 !!!!!!


See More: Multiple (nested) VLOOKUP functions

Report •


#1
January 31, 2011 at 09:35:09
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 the negative of 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.


Report •

#2
January 31, 2011 at 10:07:10
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)))


Report •

#3
January 31, 2011 at 10:20:29
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)))


Report •

Related Solutions

#4
January 31, 2011 at 10:58:31
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.


Report •

#5
January 31, 2011 at 11:21:13
Thanks.

Report •


Ask Question