VLOOKUP and IFERROR

[haj284]

Hi, I have an excel project that I need a lot of help with I can't seem to grasp nested IF's or VLOOKUPS. Here is the information

Product pricing is A1 subtotal and shipping cost are D2 and E2 then the list of product supplies are A3 thru 17 and under worksheet named product prcing and shipping


The other worksheet is named invoice and Item is F15 and total is in h15 the question is

in the per unit column (invoice worksheet) enter a formula that uses table lookup in the product pricing Table (product pricing and shipping worksheet) based on the value selected in the item column. Use the IFEEROR function to display a blank cell instead of the error value. here are the two different worksheet. first i i will post the invoice worksheet then the product pricing and shipping. please help me with the formula

[TABLE="width: 601"]
<TBODY>[TR]
[TD]Item[/TD]
[TD]Column1[/TD]
[TD]Column3[/TD]
[TD]Qty[/TD]
[TD]Per Unit[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Economy Patient Gowns[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doorknob Gripper[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Giant Tv Remote[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]




[TABLE="width: 427"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal[/TD]
[TD]Shipping Cost[/TD]
[/TR]
[TR]
[TD]Adjustable Home Bed Rail[/TD]
[TD="align: right"]89.95[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6.00[/TD]
[/TR]
[TR]
[TD]Bed Cane[/TD]
[TD="align: right"]81.95[/TD]
[TD][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]9.50[/TD]
[/TR]
[TR]
[TD]Doorknob Gripper[/TD]
[TD="align: right"]4.95[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]12.50[/TD]
[/TR]
[TR]
[TD]Easy Grip Utensils[/TD]
[TD="align: right"]32.95[/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]16.00[/TD]
[/TR]
[TR]
[TD]Economy Patient Gowns[/TD]
[TD="align: right"]6.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full-Page Magnifier[/TD]
[TD="align: right"]4.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Giant TV Remote[/TD]
[TD="align: right"]34.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inflatable Shampoo Basin[/TD]
[TD="align: right"]39.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jar Opener[/TD]
[TD="align: right"]5.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lamp Switch Enlarger[/TD]
[TD="align: right"]4.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Medication Dispenser[/TD]
[TD="align: right"]135.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No Rinse Shampoo[/TD]
[TD="align: right"]34.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tilting Overbed Table[/TD]
[TD="align: right"]114.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trolley Walker[/TD]
[TD="align: right"]139.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wheelchair Poncho[/TD]
[TD="align: right"]51.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=4></COLGROUP>[/TABLE]

 

[fredlo2008]

If I understood well. You need a way to hide the errors and at the same time be able to perform calculations with the cells that have the Blank value correct?

The solution for it is to use a "0" instead of a blank value.

=IFERROR(VLOOKUP(A1,prices,2,false),0)

and then hide all the zero values. This is my favorite

Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

• Select the cells that contain the zero (0) values that you want to hide.
• On the Format menu, click Cells, and then click the Number tab.
• In the Category list, click Custom.
• In the Type box, type 0;-0;;@

More info here http://office.microsoft.com/en-us/excel-help/display-or-hide-zero-values-HP005199879.aspx

 

[haj284]

I tried that and everything is coming up zero even in cells where there should be numbers?? I wonder what I am doing wrong

 

[haj284]

I tried that and everything is coming up zero even in cells where there should be numbers?? I wonder what I am doing wrong

 

[fredlo2008]

I tried that and everything is coming up zero even in cells where there should be numbers?? I wonder what I am doing wrong

Did you tweak my formula to fit your needs? The formula was for you to use as a guide but I cannot give you a formula unless I see your workbook or I manage to replicate your particular scenario. The second one is the least likely to succeed.

Thanks

 

[haj284]

Is there a way to attach my workbook?? Or i can email it to you

 

[fredlo2008]

You can upload it to google documents of use drop-box and get the link here

 

[MARK858]

haj284, See if this helps with what fredlo2008 is saying to you

Excel Workbook
	A	B	C	D	E	F
1	Item	Column1	Column3	Qty	Per Unit	Total
2	Economy Patient Gowns	*	*	4	6.95	*
3	Doorknob Gripper	*	*	5	4.95	*
4	Giant Tv Remote	*	*	3		*
5	Medication Dispenser	*	*	*	135.95	*
6	No Rinse Shampoo	*	*	*	34.95	*
7	Tilting Overbed Table	*	*	*	114.95	*
Sheet1

Excel Workbook
	A	B	C	D	E
1	*	*	*	Subtotal	Shipping
2	Adjustable Home Bed Rail	89.95	*	0	6
3	Bed Cane	81.95	*	55	9.5
4	Doorknob Gripper	4.95	*	100	12.5
5	Easy Grip Utensils	32.95	*	150	16
6	Economy Patient Gowns	6.95	*	*	*
7	Full-Page Magnifier	4.99	*	*	*
8	Inflatable Shampoo Basin	39.95	*	*	*
9	Jar Opener	5.95	*	*	*
10	Lamp Switch Enlarger	4.95	*	*	*
11	Medication Dispenser	135.95	*	*	*
12	No Rinse Shampoo	34.95	*	*	*
13	Tilting Overbed Table	114.95	*	*	*
14	Trolley Walker	139.95	*	*	*
15	Wheelchair Poncho	51.95	*	*	*
Prices

 

[fredlo2008]

Maybe he is confused with my named range "prices" and the sheet name "Prices"

 

[MARK858]

Maybe he is confused with my named range "prices" and the sheet name "Prices"

Just in case you are right I have kept away from the named range

Excel Workbook
	A	B	C	D	E	F
1	Item	Column1	Column3	Qty	Per Unit	Total
2	Economy Patient Gowns	*	*	4	6.95	*
3	Doorknob Gripper	*	*	5	4.95	*
4	Giant Tv Remote	*	*	3		*
5	Medication Dispenser	*	*	*	135.95	*
6	No Rinse Shampoo	*	*	*	34.95	*
7	Tilting Overbed Table	*	*	*	114.95	*
Sheet1

Excel Workbook
	A	B	C	D	E
1	*	*	*	Subtotal	Shipping
2	Adjustable Home Bed Rail	89.95	*	0	6
3	Bed Cane	81.95	*	55	9.5
4	Doorknob Gripper	4.95	*	100	12.5
5	Easy Grip Utensils	32.95	*	150	16
6	Economy Patient Gowns	6.95	*	*	*
7	Full-Page Magnifier	4.99	*	*	*
8	Inflatable Shampoo Basin	39.95	*	*	*
9	Jar Opener	5.95	*	*	*
10	Lamp Switch Enlarger	4.95	*	*	*
11	Medication Dispenser	135.95	*	*	*
12	No Rinse Shampoo	34.95	*	*	*
13	Tilting Overbed Table	114.95	*	*	*
14	Trolley Walker	139.95	*	*	*
15	Wheelchair Poncho	51.95	*	*	*
product pricing and shipping