Vlookup with text Text

[billfinn]

I am trying to match the Item Number in the BOM sheet and fill in the Description and Price from the Item Master sheet. I need to keep the Item Number columns formtted as Text (I think) because we have many part numbers that begin with 0. I tried google and a few Excel forums but I haven't figured it out yet. I would appreciate any suggestions or solutions very much.

=TEXT(VLOOKUP(A3,'[H:\Quick Estimator Project\Estimating Tool06-06-2018.xlsm]Item Master'!$A:$F,1, FALSE)Text)

Thanks
Bill

 

[MUKESHY12390]

Perhaps this..

=TEXT(VLOOKUP(A3,'[H:\Quick Estimator Project\Estimating Tool06-06-2018.xlsm]Item Master'!$A:$F,1, FALSE),"@")

 

[billfinn]

Thanks for the quick response!! I tried this but I still get an error message that A Formula in This Worksheet contains one or more invalid references. I did add the space between Tool and 06-06.
Thanks much
Bill

 

[Aladin Akyurek]

Care to post what you have in A3 and in the matching row of '[H:\Quick Estimator Project\Estimating Tool06-06-2018.xlsm]Item Master'!$A:$A?

 

[billfinn]

A3 is an item number 21214 which is formatted as text due to other item numbers beginning with 0
The matching row $A$A has item numbers one of which is the 21214.
I am more than happy to post any other info needed
Thanks,
Bill

 

[Aladin Akyurek]

A3 is an item number 21214 which is formatted as text due to other item numbers beginning with 0
The matching row $A$A has item numbers one of which is the 21214.
I am more than happy to post any other info needed
Thanks,
Bill

If you want to make sure that the VLOOKUP result is text...

=VLOOKUP(A3,'[H:\Quick Estimator Project\Estimating Tool06-06-2018.xlsm]Item Master'!$A:$A,1,0)&""


If A3 must be looked up as a test value because column A of the target is text...

=VLOOKUP(A3&"",'[H:\Quick Estimator Project\Estimating Tool06-06-2018.xlsm]Item Master'!$A:$A,1,0)&""

 

[billfinn]

Day 2 of migraine fun, I should be able to figure this out but I can't. I am providing more info in hopes that someone can help me to figure out what should be a simple problem.

I am Entering the formula in Columns B of file name "H:\Quick Estimator Project\Sub Assemblies and BOM" in the "BOM" worksheet.
I am trying to retrieve data from filename "H:\Quick Estimator Project\Estimating Tool 06-06-2018" using the "Item Master" worksheet.
Item Number is in column A in both worksheets.
The BOM worksheet in the Sub Assemblies and BOM file looks like this;

[TABLE="width: 753"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item #[/TD]
[TD]Item Description[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD="colspan: 3"] LUMBER[/TD]
[/TR]
[TR]
[TD]21214[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]15.22[/TD]
[/TR]
[TR]
[TD]21216[/TD]
[TD]2x12x16 SPF.[/TD]
[TD="align: right"]17.39[/TD]
[/TR]
[TR]
[TD]21014[/TD]
[TD]2x10x14 SPF[/TD]
[TD="align: right"]10.92[/TD]
[/TR]
[TR]
[TD]21016[/TD]
[TD]2x10x16 SPF[/TD]
[TD="align: right"]12.48[/TD]
[/TR]
[TR]
[TD]2810[/TD]
[TD]2x8x10 SPF[/TD]
[TD="align: right"]6.07[/TD]
[/TR]
[TR]
[TD]2814[/TD]
[TD]2x8x14 SPF[/TD]
[TD="align: right"]8.49[/TD]
[/TR]
[TR]
[TD]2816[/TD]
[TD]2x8x16 SPF[/TD]
[TD="align: right"]9.23[/TD]
[/TR]
[TR]
[TD]269112STUD[/TD]
[TD]2X6X91.5" KD SPF [/TD]
[TD="align: right"]3.3[/TD]
[/TR]
[TR]
[TD]268ESP[/TD]
[TD]2x6x8 SPF[/TD]
[TD="align: right"]4.52[/TD]
[/TR]
[TR]
[TD]2610[/TD]
[TD]2x6x10 SPF[/TD]
[TD="align: right"]4.43[/TD]
[/TR]
[TR]
[TD]2614[/TD]
[TD]2x6x14 SPF[/TD]
[TD="align: right"]6.2[/TD]
[/TR]
[TR]
[TD]2616[/TD]
[TD]2x6x16 SPF[/TD]
[TD="align: right"]7.09[/TD]
[/TR]
[TR]
[TD]249112STUD[/TD]
[TD]2X4X91-1/2" KD STUD [/TD]
[TD="align: right"]2.23[/TD]
[/TR]
[TR]
[TD]248ESP[/TD]
[TD]2x4x8 SPF[/TD]
[TD="align: right"]2.97[/TD]
[/TR]
[TR]
[TD]2410[/TD]
[TD]2x4x10 SPF[/TD]
[TD="align: right"]2.98[/TD]
[/TR]
[TR]
[TD]2414[/TD]
[TD]2x4x14 SPF[/TD]
[TD="align: right"]4.32[/TD]
[/TR]
[TR]
[TD]2416[/TD]
[TD]2x4x16 SPF[/TD]
[TD="align: right"]5.11[/TD]
[/TR]
[TR]
[TD]14PIP[/TD]
[TD]1x4 Industrial Pine[/TD]
[TD="align: right"]0.15
[/TD]
[/TR]
</tbody>[/TABLE]

and the Item Master worksheet in the Quick Estimating Tool 06-06-2018 looks like this;

[TABLE="width: 712"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Store[/TD]
[TD]Last Cost Date[/TD]
[TD]Supplier[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]ADV20011[/TD]
[TD]100[/TD]
[TD]2/26/2018[/TD]
[TD]HUBE[/TD]
[TD]ADVANTECH SUBFLOOR ADH 24OZ GUN FOA[/TD]
[TD]14.12[/TD]
[/TR]
[TR]
[TD]BP13605[/TD]
[TD]760[/TD]
[TD]11/2/2017[/TD]
[TD]GRAH[/TD]
[TD]FIRE/SMOKE DRAFT SEALANT 10.1[/TD]
[TD]4.17[/TD]
[/TR]
[TR]
[TD]FSONE[/TD]
[TD]900[/TD]
[TD]5/23/2018[/TD]
[TD]HIL4[/TD]
[TD]FS-ONE 600ML FOIL[/TD]
[TD]15.52[/TD]
[/TR]
[TR]
[TD]PPG1414X[/TD]
[TD]820[/TD]
[TD]7/14/2017[/TD]
[TD]PITT[/TD]
[TD]PPG TOP GUN 200XI WHITE PAINTERS CA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PS600[/TD]
[TD]760[/TD]
[TD]6/22/2017[/TD]
[TD]PRIM[/TD]
[TD]SAUSAGE PACK 20OZ CAULKER[/TD]
[TD]41.6[/TD]
[/TR]
[TR]
[TD]QB2[/TD]
[TD]400[/TD]
[TD]10/27/2017[/TD]
[TD]ORGI[/TD]
[TD]QBOND SMALL REPAIR KIT[/TD]
[TD]7.42[/TD]
[/TR]
[TR]
[TD]RRCAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WIPE NEW RECOLOR KIT[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]SS10020[/TD]
[TD]740[/TD]
[TD]6/12/2015[/TD]
[TD]NOR7[/TD]
[TD]SMOKE SOUND SEALANT WHT 20OZ[/TD]
[TD]4.57[/TD]
[/TR]
[TR]
[TD]WB005023[/TD]
[TD]100[/TD]
[TD]10/6/2015[/TD]
[TD]AVW[/TD]
[TD]PROSTICK 65MP SPRAY ADHES 17OZ W AD[/TD]
[TD]4.98[/TD]
[/TR]
[TR]
[TD]55368[/TD]
[TD]760[/TD]
[TD]5/7/2018[/TD]
[TD]ORGI[/TD]
[TD]JBWELD 27106 RED THREADLOCKER 6ML[/TD]
[TD]2.7[/TD]
[/TR]
[TR]
[TD]375568[/TD]
[TD]500[/TD]
[TD]5/31/2018[/TD]
[TD]ORGI[/TD]
[TD]110012 SHOE GOO ADHESIVE 3.7OZ[/TD]
[TD]2.79[/TD]
[/TR]
[TR]
[TD]410571[/TD]
[TD]760[/TD]
[TD]5/29/2018[/TD]
[TD]ORGI[/TD]
[TD]9OZ MIRACLE BOND EPOXY[/TD]
[TD]12.71[/TD]
[/TR]
[TR]
[TD]1002583[/TD]
[TD]760[/TD]
[TD]12/21/2017[/TD]
[TD]ORGI[/TD]
[TD]3M IC-15WB FIRE BARRIER 10.1 FL[/TD]
[TD]6.15[/TD]
[/TR]
[TR]
[TD]1155845[/TD]
[TD]400[/TD]
[TD]6/1/2018[/TD]
[TD]ORGI[/TD]
[TD]ELMERS RUBBER CEMENT 4OZ[/TD]
[TD]1.44[/TD]
[/TR]
[TR]
[TD]1241090[/TD]
[TD]100[/TD]
[TD]3/19/2018[/TD]
[TD]ORGI[/TD]
[TD]6221 INST BOND WD ADH THCK 2OZ[/TD]
[TD]5.66[/TD]
[/TR]
[TR]
[TD]1256098[/TD]
[TD]810[/TD]
[TD]5/4/2018[/TD]
[TD]ORGI[/TD]
[TD]JBWELD 24206 BLUE THREADLOCKER 6ML[/TD]
[TD]2.59[/TD]
[/TR]
[TR]
[TD]1256106[/TD]
[TD]760[/TD]
[TD]3/7/2018[/TD]
[TD]ORGI[/TD]
[TD]EPOXY ADHESIVE QUICK STTG 25ML[/TD]
[TD]2.88[/TD]
[/TR]
[TR]
[TD]1327980[/TD]
[TD]400[/TD]
[TD]1/25/2018[/TD]
[TD]ORGI[/TD]
[TD]LOCTITE VOC MIRROR MASTIC ADHESIVE[/TD]
[TD]1.88[/TD]
[/TR]
</tbody>[/TABLE]

I want to locate the Item Number from the Item Master sheet and populate the BOM sheet with Description and Cost for that item in the appropriate columns.
Thanks in advance for any help to get this resolved.

 

[billfinn]

I figured out my issue, got it working now. Funny thing is that every time I paste the formula into a cell a screen comes up for me to select the source file. I got all but 4 of the cells in my sheet populated with my formula. Those 4 give me a Value Not Available error. All 4 have an Item Number that is numeric and I have that column formatted as text to keep leading zeroes on other Items. There are over 100 other numeric part numbers that are not causing an issue.
If anyone has any idea as to what could cause that issue I would be grateful for a clue.
Thanks,
Bill