Combine a VLOOKUP and IF Formula depending on Search Criteri

[DougStroud]

Good Morning,
I am working on combining a VLOOKUP and IF Formula and need some assistance.

I am looking to return a shipping weight depending on the item type, the problem I am having is this; the item may be in a category or it may be in a sub-category.

If the item type resides in the category, no problem, my VLOOKUP finds the shipping weight. If the item type is classfied down one more level in a sub-category the VLOOKUP needs to look here.

If the item type has no sub-category listing, then this field is blank. I was thinking of accomplishing this by combining an IF formula that basically says look in the sub-category field first, if nothing exists, look in the category field.
Column V= Category Field
Column W= Sub-Category Field

Attempted Formula yielded a #NA Result:
Formula in full is below:

=IF(VLOOKUP(W4,'[Departments & Categories.xls]Shipping Weights'!$B$1:$D$324,2,FALSE)="",VLOOKUP(V4,'[Departments & Categories.xls]Shipping Weights'!$B$1:$D$324,2,FALSE))

O4 shows the #NA Result using the IF and VLOOKUP
P4 shows the VLOOKUP w/o the IF. It is listing the Item Type's Actual Weight and this Item Type does not have a sub-category so, no problem. But the following two items have sub-categories, so the my combined formula is required. But, I am only using the VLOOKUP here, not the combined/nested formula that will be required to return the correct shipping and actual weight.

HTML View:

EC-Orphans(10-24_04-56)b.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
3	SKU	Name	Use this column to change sizing	Parent SKU	Description	Specs	URL	Price	Sale Price	Color	Size	Trim 1	Trim 2	Trim 3	s/h Weight	actual Weight	Date Avail	Date Added	Qty	Manufacturer	Dept	Category	Subcategory	Re Orderable
4	41165	411 SKATEBOARD DVD #65						19.99							#N/A	0.5		2005-10-17 10:32:17	2	411	Skate	Videos		Y
5	41165	411 SKATEBOARD DVD #65						19.99							1	0.5		2005-10-17 10:32:17	2	411	Accessories	Videos	Skate	Y
6	686RANGEBLKL	686 RANGE JACKET RED L		686RANGEBLK				200			L				#N/A	#N/A		2005-10-17 10:38:06	1	686	Snow	Guys Outerwear	Guys Jackets	Y
EC Products

Thanks,

Doug S.

 

[njimack]

Try something like this:

=IF(W4="",VLOOKUP(V4,'[Departments & Categories.xls]Shipping Weights'!$B$1:$D$324,2,FALSE),VLOOKUP(W4,'[Departments & Categories.xls]Shipping Weights'!$B$1:$D$324,2,FALSE)


The reason you're returning N/A is because you're trying to lookup W4, which has no value. Therefore, you need 'IF(W4=""' at the beginning. If W4 is blank, the formula will lookup V4, but if W4 has a value, it will lookup VW4

 

[Rijnsent]

Vlookup & if

Hi Doug,

your formula is indeed not working... The problem is that the first part of your if statement:

=IF(VLOOKUP(W4,'[Departments & Categories.xls]Shipping Weights'!$B$1:$D$324,2,FALSE)="";

does not work properly. If your value cannot be found, the VLOOKUP will not respond with "", but with #N/A... So the second part of your if (false) will never be reached. Anyhow, the way to solve this is with the ISERROR() formula.
IF(ISERROR(VLOOKUP(W4,'[Departments & Categories.xls]Shipping Weights'!$B$1:$D$324,2,FALSE))=FALSE;VLOOKUP(W4,'[Departments & Categories.xls]Shipping Weights'!$B$1:$D$324,2,FALSE);"Here goes the other searcharea SubProduct")

Greetz,

Koen

 

[DougStroud]

Thanks Njimack,

That worked. And, thanks for the explanation, its the old fish analogy....


Doug S.

 

[DougStroud]

Koen,
Thanks, Njimack's submission hit it on the head.

Regards...