Vlookup

[brennanv]

I am having a lot of trouble getting the vlookup formula to work for me. i've trimmed the date, made sure all the data was identical and still cannot get it to work. I have no idea what else to do. I've literally spent over an hour trying to get this simple formula to work and cannot figure it out to save my life.

The formula i am currently using is =VLOOKUP(B4,Sheet3!$A$4:$L$34,1,false)

 

[Momentman]

What do you have in B4 and in SHeet3!A4:A34? Sample data maybe

 

[brennanv]

Im trying to get the formula to return a portfolio code based on an account number. the data in b4 is the account number and all of the data in sheet 3 is the lookup data.

 

[Momentman]

Im trying to get the formula to return a portfolio code based on an account number. the data in b4 is the account number and all of the data in sheet 3 is the lookup data.

Sure, I get that

Have you checked that the Account number is formatted same in both the lookup value and in the Lookup range. i.e one is not formatted as text and the other as number

 

[brennanv]

Sure, I get that

Have you checked that the Account number is formatted same in both the lookup value and in the Lookup range. i.e one is not formatted as text and the other as number

Yes, everything is formatted as numbers. the error message reads "a value is not available to the formula or function"

 

[Momentman]

You obviously mean #N/A. Are you certain that the value exists? Have you checked manually? If the data is not confidential(which I doubt ), you can post a sample here so we can see whats going on?

 

[brennanv]

Great,

Do I attach a file? or how do I go about posting a sample? (new to this site, sorry)

Reference data

[TABLE="width: 901"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 908"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]armstroc[/TD]
[TD]123[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD]$130,440.00[/TD]
[TD]130440[/TD]
[TD]ms[/TD]
[TD]Taxable[/TD]
[TD]1/10/13[/TD]
[TD]6/15/04[/TD]
[TD]0.65[/TD]
[/TR]
[TR]
[TD]briggsw[/TD]
[TD]124[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD]$100,000.00[/TD]
[TD]100000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]1/15/13[/TD]
[TD]10/12/01[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]nelsonc[/TD]
[TD]125[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD]$250,000.00[/TD]
[TD]250000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]2/6/13[/TD]
[TD]4/24/02[/TD]
[TD]1.375[/TD]
[/TR]
[TR]
[TD]jacquei[/TD]
[TD]126[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]NonDiscr[/TD]
[TD]$20,447.00[/TD]
[TD]20447[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/7/13[/TD]
[TD]6/3/93[/TD]
[TD]0.75[/TD]
[/TR]
[TR]
[TD]florlind[/TD]
[TD]127[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Fixed Income[/TD]
[TD]$170,000.00[/TD]
[TD]170000[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]2/8/13[/TD]
[TD]12/29/10[/TD]
[TD]0.75[/TD]
[/TR]
[TR]
[TD]florlira[/TD]
[TD]128[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Fixed Income[/TD]
[TD]$17,637.00[/TD]
[TD]17637[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/8/13[/TD]
[TD]12/29/10[/TD]
[TD]0.75[/TD]
[/TR]
[TR]
[TD]florlsep[/TD]
[TD]129[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Fixed Income[/TD]
[TD]$55,417.81[/TD]
[TD]55417.81[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/8/13[/TD]
[TD]12/29/10[/TD]
[TD]0.75[/TD]
[/TR]
[TR]
[TD]giaved1[/TD]
[TD]130[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD]$92,000.00[/TD]
[TD]92000[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]2/22/13[/TD]
[TD]3/29/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]giaved2[/TD]
[TD]131[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD]$93,077.28[/TD]
[TD]93077.28[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/22/13[/TD]
[TD]3/30/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]giaved3[/TD]
[TD]132[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Select Dividend[/TD]
[TD]$70,000.00[/TD]
[TD]70000[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/22/13[/TD]
[TD]3/30/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]gelernte[/TD]
[TD]133[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$615,488.00[/TD]
[TD]120000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]3/20/13[/TD]
[TD]12/31/89[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]moyira[/TD]
[TD]134[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Select[/TD]
[TD="align: right"]$615,488.00[/TD]
[TD]619071.95[/TD]
[TD]Schwab[/TD]
[TD] [/TD]
[TD]3/21/13[/TD]
[TD]11/29/10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]levyh[/TD]
[TD]135[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Tech Value[/TD]
[TD="align: right"]$58,348.73[/TD]
[TD]50000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/4/13[/TD]
[TD]3/16/05[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]delziofm[/TD]
[TD]136[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$30,681.28[/TD]
[TD]100000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/8/13[/TD]
[TD]7/10/07[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]schlagss[/TD]
[TD]137[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]NonDiscr[/TD]
[TD="align: right"]$59,993.67[/TD]
[TD]93000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/15/13[/TD]
[TD]2/7/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bornt[/TD]
[TD]138[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Dividend Value[/TD]
[TD="align: right"]$213,521.58[/TD]
[TD]100000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/18/13[/TD]
[TD]10/22/09[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]mccarthi[/TD]
[TD]139[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$91,653.22[/TD]
[TD]125000[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]4/26/13[/TD]
[TD]12/22/06[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]mccartri[/TD]
[TD]140[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$65,535.33[/TD]
[TD]75000[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]4/26/13[/TD]
[TD]12/22/06[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]emerick1[/TD]
[TD]141[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD="align: right"]$148,929.44[/TD]
[TD]113728[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]6/19/13[/TD]
[TD]7/12/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]emerick2[/TD]
[TD]142[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD="align: right"]$348,984.40[/TD]
[TD]370441.37[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]6/19/13[/TD]
[TD]7/26/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]deibelct[/TD]
[TD]143[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$327,487.52[/TD]
[TD]251819.5[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]6/26/13[/TD]
[TD]1/17/06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cridge[/TD]
[TD]144[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]DPS Conservative[/TD]
[TD="align: right"]$237,923.14[/TD]
[TD]500000[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]7/8/13[/TD]
[TD]6/26/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]pateli[/TD]
[TD]145[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$262,061.26[/TD]
[TD]82718[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]7/12/13[/TD]
[TD]2/1/02[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]patemi[/TD]
[TD]146[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$703,300.48[/TD]
[TD]304092[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]7/12/13[/TD]
[TD]2/1/02[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]kuninmi[/TD]
[TD]147[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$519,254.27[/TD]
[TD]1000000[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]7/24/13[/TD]
[TD]3/4/08[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]fadcop[/TD]
[TD]148[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$281,345.80[/TD]
[TD]1000000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]7/31/13[/TD]
[TD]11/29/04[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]mfifound[/TD]
[TD]149[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Opp Growth[/TD]
[TD="align: right"]$529,671.61[/TD]
[TD]250000[/TD]
[TD]fidinst[/TD]
[TD] [/TD]
[TD]8/16/13[/TD]
[TD]6/1/11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]barabt2[/TD]
[TD]150[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$0.00[/TD]
[TD]290677[/TD]
[TD]Schwab[/TD]
[TD] [/TD]
[TD]9/5/13[/TD]
[TD]8/1/03[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]cauffman[/TD]
[TD]151[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Opp Moderate[/TD]
[TD="align: right"]$321,509.96[/TD]
[TD]259524[/TD]
[TD]Schwab[/TD]
[TD] [/TD]
[TD]10/31/13[/TD]
[TD]11/23/10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]youngert[/TD]
[TD]152[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$463,002.24[/TD]
[TD]331362.86[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]11/18/13[/TD]
[TD]9/9/09[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]oneilld[/TD]
[TD]153[/TD]
[TD]Closed[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$3,334,445.16[/TD]
[TD]124582[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]11/26/13[/TD]
[TD]2/16/93[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Vlookup should go where it says portfolio code

[TABLE="width: 901"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Portfolio Status[/TD]
[TD]Broker Act Number:[/TD]
[TD]Channel 1[/TD]
[TD]Strategy:[/TD]
[TD]Total Market Value[/TD]
[TD]Initial Value[/TD]
[TD]Broker[/TD]
[TD]Tax Status[/TD]
[TD]Close Date[/TD]
[TD]Start Date[/TD]
[TD]Rate 1[/TD]
[TD]Portolio code[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]123[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD]$130,440.00[/TD]
[TD]130440[/TD]
[TD]ms[/TD]
[TD]Taxable[/TD]
[TD]1/10/13[/TD]
[TD]6/15/04[/TD]
[TD]0.65[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]124[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD]$100,000.00[/TD]
[TD]100000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]1/15/13[/TD]
[TD]10/12/01[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]125[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD]$250,000.00[/TD]
[TD]250000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]2/6/13[/TD]
[TD]4/24/02[/TD]
[TD]1.375[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]126[/TD]
[TD]Yes[/TD]
[TD]NonDiscr[/TD]
[TD]$20,447.00[/TD]
[TD]20447[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/7/13[/TD]
[TD]6/3/93[/TD]
[TD]0.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]127[/TD]
[TD]Yes[/TD]
[TD]Fixed Income[/TD]
[TD]$170,000.00[/TD]
[TD]170000[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]2/8/13[/TD]
[TD]12/29/10[/TD]
[TD]0.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]128[/TD]
[TD]Yes[/TD]
[TD]Fixed Income[/TD]
[TD]$17,637.00[/TD]
[TD]17637[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/8/13[/TD]
[TD]12/29/10[/TD]
[TD]0.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]129[/TD]
[TD]Yes[/TD]
[TD]Fixed Income[/TD]
[TD]$55,417.81[/TD]
[TD]55417.81[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/8/13[/TD]
[TD]12/29/10[/TD]
[TD]0.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]130[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD]$92,000.00[/TD]
[TD]92000[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]2/22/13[/TD]
[TD]3/29/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]131[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD]$93,077.28[/TD]
[TD]93077.28[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/22/13[/TD]
[TD]3/30/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]132[/TD]
[TD]Yes[/TD]
[TD]Select Dividend[/TD]
[TD]$70,000.00[/TD]
[TD]70000[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]2/22/13[/TD]
[TD]3/30/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]133[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$615,488.00[/TD]
[TD]120000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]3/20/13[/TD]
[TD]12/31/89[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]134[/TD]
[TD]Yes[/TD]
[TD]Select[/TD]
[TD="align: right"]$615,488.00[/TD]
[TD]619071.95[/TD]
[TD]Schwab[/TD]
[TD] [/TD]
[TD]3/21/13[/TD]
[TD]11/29/10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]135[/TD]
[TD]Yes[/TD]
[TD]Tech Value[/TD]
[TD="align: right"]$58,348.73[/TD]
[TD]50000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/4/13[/TD]
[TD]3/16/05[/TD]
[TD]0.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]136[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$30,681.28[/TD]
[TD]100000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/8/13[/TD]
[TD]7/10/07[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]137[/TD]
[TD]Yes[/TD]
[TD]NonDiscr[/TD]
[TD="align: right"]$59,993.67[/TD]
[TD]93000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/15/13[/TD]
[TD]2/7/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]138[/TD]
[TD]Yes[/TD]
[TD]Dividend Value[/TD]
[TD="align: right"]$213,521.58[/TD]
[TD]100000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]4/18/13[/TD]
[TD]10/22/09[/TD]
[TD]1.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]139[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$91,653.22[/TD]
[TD]125000[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]4/26/13[/TD]
[TD]12/22/06[/TD]
[TD]1.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]140[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$65,535.33[/TD]
[TD]75000[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]4/26/13[/TD]
[TD]12/22/06[/TD]
[TD]1.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]141[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD="align: right"]$148,929.44[/TD]
[TD]113728[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]6/19/13[/TD]
[TD]7/12/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]142[/TD]
[TD]Yes[/TD]
[TD]Country Rotation[/TD]
[TD="align: right"]$348,984.40[/TD]
[TD]370441.37[/TD]
[TD]fidinst[/TD]
[TD]Non-Taxable[/TD]
[TD]6/19/13[/TD]
[TD]7/26/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]143[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$327,487.52[/TD]
[TD]251819.5[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]6/26/13[/TD]
[TD]1/17/06[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]144[/TD]
[TD]Yes[/TD]
[TD]DPS Conservative[/TD]
[TD="align: right"]$237,923.14[/TD]
[TD]500000[/TD]
[TD]fidinst[/TD]
[TD]Taxable[/TD]
[TD]7/8/13[/TD]
[TD]6/26/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]145[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$262,061.26[/TD]
[TD]82718[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]7/12/13[/TD]
[TD]2/1/02[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]146[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$703,300.48[/TD]
[TD]304092[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]7/12/13[/TD]
[TD]2/1/02[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]147[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$519,254.27[/TD]
[TD]1000000[/TD]
[TD]Schwab[/TD]
[TD]Non-Taxable[/TD]
[TD]7/24/13[/TD]
[TD]3/4/08[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]148[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$281,345.80[/TD]
[TD]1000000[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]7/31/13[/TD]
[TD]11/29/04[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]149[/TD]
[TD]Yes[/TD]
[TD]Opp Growth[/TD]
[TD="align: right"]$529,671.61[/TD]
[TD]250000[/TD]
[TD]fidinst[/TD]
[TD] [/TD]
[TD]8/16/13[/TD]
[TD]6/1/11[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]150[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$0.00[/TD]
[TD]290677[/TD]
[TD]Schwab[/TD]
[TD] [/TD]
[TD]9/5/13[/TD]
[TD]8/1/03[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]151[/TD]
[TD]Yes[/TD]
[TD]Opp Moderate[/TD]
[TD="align: right"]$321,509.96[/TD]
[TD]259524[/TD]
[TD]Schwab[/TD]
[TD] [/TD]
[TD]10/31/13[/TD]
[TD]11/23/10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]152[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$463,002.24[/TD]
[TD]331362.86[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]11/18/13[/TD]
[TD]9/9/09[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]153[/TD]
[TD]Yes[/TD]
[TD]Value[/TD]
[TD="align: right"]$3,334,445.16[/TD]
[TD]124582[/TD]
[TD]Schwab[/TD]
[TD]Taxable[/TD]
[TD]11/26/13[/TD]
[TD]2/16/93[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

So what should the answer be for the first example, co it looks like an Index-match rather than a Vlookup

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
3	Portfolio Status	Broker Act Number:	Channel 1	Strategy:	Total Market Value	Initial Value	Broker	Tax Status	Close Date	Start Date	Rate 1	Portolio code
4	Closed	123	Yes	Value	$130,440.00	130440	ms	Taxable	######	6/15/04	0.65	armstroc
5	Closed	124	Yes	Value	$100,000.00	100000	Schwab	Taxable	1/15/13	######	1	briggsw
6	Closed	125	Yes	Value	$250,000.00	250000	Schwab	Taxable	######	4/24/02	1.375	nelsonc
7	Closed	126	Yes	NonDiscr	$20,447.00	20447	fidinst	Non-Taxable	######	######	0.75	jacquei
8	Closed	127	Yes	Fixed Income	$170,000.00	170000	fidinst	Taxable	######	12/29/10	0.75	florlind
9	Closed	128	Yes	Fixed Income	$17,637.00	17637	fidinst	Non-Taxable	######	12/29/10	0.75	florlira
10	Closed	129	Yes	Fixed Income	$55,417.81	55417.81	fidinst	Non-Taxable	######	12/29/10	0.75	florlsep
11	Closed	130	Yes	Country Rotation	$92,000.00	92000	fidinst	Taxable	2/22/13	3/29/12		giaved1
12	Closed	131	Yes	Country Rotation	$93,077.28	93077.28	fidinst	Non-Taxable	2/22/13	3/30/12		giaved2
13	Closed	132	Yes	Select Dividend	$70,000.00	70000	fidinst	Non-Taxable	2/22/13	3/30/12		giaved3
14	Closed	133	Yes	Value	$615,488.00	120000	Schwab	Taxable	3/20/13	12/31/89		gelernte
15	Closed	134	Yes	Select	$615,488.00	619072	Schwab		3/21/13	11/29/10		moyira
16	Closed	135	Yes	Tech Value	$58,348.73	50000	Schwab	Taxable	######	3/16/05	0.7	levyh
17	Closed	136	Yes	Value	$30,681.28	100000	Schwab	Taxable	######	######	1	delziofm
18	Closed	137	Yes	NonDiscr	$59,993.67	93000	Schwab	Taxable	4/15/13	######		schlagss
19	Closed	138	Yes	Dividend Value	$213,521.58	100000	Schwab	Taxable	4/18/13	10/22/09	1.25	bornt
20	Closed	139	Yes	Value	$91,653.22	125000	Schwab	Non-Taxable	4/26/13	12/22/06	1.5	mccarthi
21	Closed	140	Yes	Value	$65,535.33	75000	Schwab	Non-Taxable	4/26/13	12/22/06	1.5	mccartri
22	Closed	141	Yes	Country Rotation	$148,929.44	113728	fidinst	Taxable	6/19/13	######		emerick1
23	Closed	142	Yes	Country Rotation	$348,984.40	370441.4	fidinst	Non-Taxable	6/19/13	7/26/12		emerick2
24	Closed	143	Yes	Value	$327,487.52	251819.5	Schwab	Taxable	6/26/13	1/17/06		deibelct
25	Closed	144	Yes	DPS Conservative	$237,923.14	500000	fidinst	Taxable	######	6/26/12		cridge
26	Closed	145	Yes	Value	$262,061.26	82718	Schwab	Non-Taxable	######	######	2	pateli
27	Closed	146	Yes	Value	$703,300.48	304092	Schwab	Non-Taxable	######	######	2	patemi
28	Closed	147	Yes	Value	$519,254.27	1000000	Schwab	Non-Taxable	7/24/13	######	1	kuninmi
29	Closed	148	Yes	Value	$281,345.80	1000000	Schwab	Taxable	7/31/13	11/29/04	1	fadcop
30	Closed	149	Yes	Opp Growth	$529,671.61	250000	fidinst		8/16/13	######	1	mfifound
31	Closed	150	Yes	Value	$0.00	290677	Schwab		######	######	1	barabt2
32	Closed	151	Yes	Opp Moderate	$321,509.96	259524	Schwab		10/31/13	11/23/10		cauffman
33	Closed	152	Yes	Value	$463,002.24	331362.9	Schwab	Taxable	11/18/13	######	1	youngert
34	Closed	153	Yes	Value	$3,334,445.16	124582	Schwab	Taxable	11/26/13	2/16/93	1	oneilld
Period2

Cell Formulas
Range	Formula
L4	=INDEX(Sheet3!$A$4:$A$34,MATCH(B4,Sheet3!$B$4:$B$34,0))

 

[brennanv]

So what should the answer be for the first example, co it looks like an Index-match rather than a Vlookup




[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L4[/TH]
[TD="align: left"]=INDEX(Sheet3!$A$4:$A$34,MATCH(B4,Sheet3!$B$4:$B$34,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Could you explain the difference between the vlookup and the index match?

 

[Access Beginner]

I am having a lot of trouble getting the vlookup formula to work for me. i've trimmed the date, made sure all the data was identical and still cannot get it to work. I have no idea what else to do. I've literally spent over an hour trying to get this simple formula to work and cannot figure it out to save my life.

The formula i am currently using is =VLOOKUP(B4,Sheet3!$A$4:$L$34,1,false)

Are you trying to return column 1 in the above vlookup? Doesn't make sense that you would try and return column 1 of the lookup table.