# 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

#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:D2Table B is in F1:G1F1 contains the date you are looking upG1 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 • 