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;

Item #	Item Description	Cost
LUMBER
21214	#N/A	15.22
21216	2x12x16 SPF.	17.39
21014	2x10x14 SPF	10.92
21016	2x10x16 SPF	12.48
2810	2x8x10 SPF	6.07
2814	2x8x14 SPF	8.49
2816	2x8x16 SPF	9.23
269112STUD	2X6X91.5" KD SPF	3.3
268ESP	2x6x8 SPF	4.52
2610	2x6x10 SPF	4.43
2614	2x6x14 SPF	6.2
2616	2x6x16 SPF	7.09
249112STUD	2X4X91-1/2" KD STUD	2.23
248ESP	2x4x8 SPF	2.97
2410	2x4x10 SPF	2.98
2414	2x4x14 SPF	4.32
2416	2x4x16 SPF	5.11
14PIP	1x4 Industrial Pine	0.15

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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