Help with Vlookups

[agerrard]

Hi all,

Im using Excel 2003.

My probem is to do with the formulas in the columns H, I, J & K. So my formula is (in cell H2787):

=IF(('Actual Scan Data'!DK4)*1000=0,'Model Summary Sheet'!H861,('Actual Scan Data'!DK4)*1000

This was fine before i realised that i needed to put a vlookup in the formula as i need to vlookup using the "EAN" code in column A. I dont need to vlookup for the values in the Model Summary Sheet, its just for the data in the Actual Scan Data.

Also if there happens to be any blank cells in the Actual Scan Data sheet wont that return errors? so is there a way to take those into account as well?

Hope this is enough information. Thanks in advance!

Sample Sheet_1.xls
	A	B	C	D	E	F	G	H	I	J	K
2786	EAN	Retailer'sGM	Size	Brand	Item	ItemCode	Assortment	Wk_1	Wk_2	Wk_3	Wk_4
2787	9310052551216	Actual&PredictedRSV$	250g	Brand_1	Product_1	11158000	Product_1	$32,840.1	$30,482.8	$19,973.0	$12,042.3
2788	9310052315207	Actual&PredictedRSV$	1kg	Brand_1	Product_2	11497000	Product_2	$5,434.3	$5,434.3	$5,434.3	$5,434.3
2789	9310052351205	Actual&PredictedRSV$	250g	Brand_1	Product_3	11150000	Product_3	$-	$-	$-	$-
2790	9310052333201	Actual&PredictedRSV$	500g	Brand_1	Product_4	11151000	Product_4	$-	$-	$-	$-
2791	9310052433239	Actual&PredictedRSV$	250g	Brand_1	Product_5	11166000	Product_5	$83,964.1	$89,661.5	$67,216.7	$72,976.3
2792	9310052111434	Actual&PredictedRSV$	250g	Brand_2	Product_6	11167000	Product_6	$106,443.9	$61,678.9	$59,467.9	$60,411.9
2793	9310052111441	Actual&PredictedRSV$	500g	Brand_2	Product_7	11170001	Product_7	$74,429.6	$74,149.0	$74,823.4	$70,370.2
2794	9310052415204	Actual&PredictedRSV$	1kg	Brand_2	Product_8	11165000	Product_8	$83,613.0	$83,613.0	$6.5	$83,613.0
2795	9310052451202	Actual&PredictedRSV$	250g	Brand_3	Product_9	11159000	Product_9	$69,923.2	$69,923.2	$69,923.2	$69,923.2
2796	9310052551209	Actual&PredictedRSV$	250g	Brand_3	Product_10	11155000	Product_10	$44,347.2	$23,122.4	$24,471.2	$24,061.2
Model Summary Sheet

 

[agerrard]

Ok just had a play and this is the formula that i hoped would work, but no luck

If you look at cell H2786 and the formula reads:

=IF(VLOOKUP(A2787,'Actual Scan Data'!$DJ:$FJ,2,FALSE)*1000=0,'Model Summary Sheet'!H861,VLOOKUP(A2787,'Actual Scan Data'!$DJ:$FJ,2,FALSE)*1000)

So i hoped the vlookup would go away and find the data for that week based on the EAN code (taken from the Actual Scan Data sheet). If data was there then that gets put into the cell. But if no data is in that range, then my forecast data (from the "model summary sheet") would be put in its place.

This was happening before, but my data from the "Actual Scan Data" sheet was not corresponding to the correct item as i wasnt using vlookups.

Hope this makes sense. Any help appreciated

Sample Sheet_1.xls
	A	B	C	D	E	F	G	H
2786	EAN	Retailer'sGM	Size	Brand	Item	ItemCode	Assortment	Wk_1
2787	9310052551216	Actual&PredictedRSV$	250g	Brand_1	Product_1	11158000	Product_1	#N/A
2788	9310052315207	Actual&PredictedRSV$	1kg	Brand_1	Product_2	11497000	Product_2	$5,434.3
Model Summary Sheet

 

[agerrard]

ok, so working through my formula before i found that it was ok. It was just the fact that the EAN reference numbers in the "actual scan data" sheet weren't formatted as numbers so thats why it wasn't working. phew!

but next problem is that i get #N/A errors in some of my cells. so how do i write them out whilst keeping my formula? (in the last post)

cheers!

 

[agerrard]

So here's what i thought would work:

=IF(ISERROR(IF(VLOOKUP($A2804,'Actual Scan Data'!$FL:$HM,'Actual Scan Data'!FN$2,FALSE)=0,0,'Model Summary Sheet'!H878,VLOOKUP($A2804,'Actual Scan Data'!$FL:$HM,'Actual Scan Data'!FN$2,FALSE))

so i inserted in ISERROR into my formula, but that still doesn't work, so not really sure what im doing wrong.

 

[markoakes]

This will enter a Blank instead of #N/A

=IF(ISERROR(IF(VLOOKUP(A2787,'Actual Scan Data'!$DJ:$FJ,2,FALSE)*1000=0,'Model Summary Sheet'!H861,VLOOKUP(A2787,'Actual Scan Data'!$DJ:$FJ,2,FALSE)*1000)),"",IF(VLOOKUP(A2787,'Actual Scan Data'!$DJ:$FJ,2,FALSE)*1000=0,'Model Summary Sheet'!H861,VLOOKUP(A2787,'Actual Scan Data'!$DJ:$FJ,2,FALSE)*1000))

 

[agerrard]

hi markoakes,

thanks for getting back to me. that formula works great for the cells where i was getting the errors. but when i copied to the other cells where i know there is data, i get a "false" value that comes up in each of those cells?

so not sure why that is. is there a way to get around this do you think?

 

[markoakes]

If you are getting a "False" then the Look Up formula is not finding what it is looking for on the 'Actual Scan Data'. If you use the formula that you posted in your second formula you should also get "False". You need to review the 'Actual Scan Data' and see why the Look Up can't find the data it is serching for.

 

[agerrard]

OK so here is where im currently at.

My new formula is working perfectly for the cells where i know there is data in both the ranges (so either in the actual scan summary sheet or the model summary sheet)

This is the formula that is working (from cell H2787)

=IF(VLOOKUP($A2787,'Actual Scan Data'!$FL:$HM,'Actual Scan Data'!FN$2,FALSE)=0,'Model Summary Sheet'!H861,VLOOKUP($A2787,'Actual Scan Data'!$FL:$HM,'Actual Scan Data'!FN$2,FALSE))

Sample Sheet_1.xls
	A	B	C	D	E	F	G	H
2786	EAN	Retailer'sGM	Size	Brand	Item	ItemCode	Assortment	Wk_1
2787	9310052551216	Actual&PredictedRSV$	250g	Brand_1	Product_1	11158000	Product_1	$26,988.3
2788	9310052315207	Actual&PredictedRSV$	1kg	Brand_1	Product_2	11497000	Product_2	$32,840.1
2789	9310052351205	Actual&PredictedRSV$	250g	Brand_1	Product_3	11150000	Product_3	$-
2790	9310052333201	Actual&PredictedRSV$	500g	Brand_1	Product_4	11151000	Product_4	$-
2791	9310052433239	Actual&PredictedRSV$	250g	Brand_1	Product_5	11166000	Product_5	$20,424.4
2792	9310052111434	Actual&PredictedRSV$	250g	Brand_2	Product_6	11167000	Product_6	$34,162.6
2793	9310052111441	Actual&PredictedRSV$	500g	Brand_2	Product_7	11170001	Product_7	$27,989.5
2794	9310052415204	Actual&PredictedRSV$	1kg	Brand_2	Product_8	11165000	Product_8	$83,964.1
2795	9310052451202	Actual&PredictedRSV$	250g	Brand_3	Product_9	11159000	Product_9	$106,443.9
2796	9310052551209	Actual&PredictedRSV$	250g	Brand_3	Product_10	11155000	Product_10	$44,347.2
2797	9310052533205	Actual&PredictedRSV$	500g	Brand_3	Product_11	11156000	Product_11	$23,272.0
2798	9310052433253	Actual&PredictedRSV$	500g	Brand_3	Product_12	11176000	Product_12	$35,716.9
2799	9310264911099	Actual&PredictedRSV$	500g	Brand_4	Product_13	11175001	Product_13	$34,568.9
2800	9310052433208	Actual&PredictedRSV$	500g	Brand_5	Product_14	11169001	Product_14	$74,429.6
2801	9414832141502	Actual&PredictedRSV$	125g	Brand_6	Product_15	27244001	Product_15	$-
2802	9414832191026	Actual&PredictedRSV$	125g	Brand_7	Product_16	27285001	Product_16	$-
2803	9414832260036	Actual&PredictedRSV$	500g	Brand_8	Product_17	27908001	Product_17	$-
2804	9414832111802	Actual&PredictedRSV$	250g	Brand_9	Product_18	27250001	Product_18	#N/A
Model Summary Sheet

However this doesnt work when there are #N/A errors (see cell H2804). So in my next sample i've copied the formula that markoakes gave me to show you what happened.

So its removed the #N/A error which is great, but when i copied that formula to the other cells its returned a FALSE value. And i know for sure that there are data for those items, so not sure how to fix this.

Sample Sheet_1.xls
	A	B	C	D	E	F	G	H
2786	EAN	Retailer'sGM	Size	Brand	Item	ItemCode	Assortment	Wk_1
2787	9310052551216	Actual&PredictedRSV$	250g	Brand_1	Product_1	11158000	Product_1	FALSE
2788	9310052315207	Actual&PredictedRSV$	1kg	Brand_1	Product_2	11497000	Product_2	FALSE
2789	9310052351205	Actual&PredictedRSV$	250g	Brand_1	Product_3	11150000	Product_3	$-
2790	9310052333201	Actual&PredictedRSV$	500g	Brand_1	Product_4	11151000	Product_4	$-
2791	9310052433239	Actual&PredictedRSV$	250g	Brand_1	Product_5	11166000	Product_5	FALSE
2792	9310052111434	Actual&PredictedRSV$	250g	Brand_2	Product_6	11167000	Product_6	FALSE
2793	9310052111441	Actual&PredictedRSV$	500g	Brand_2	Product_7	11170001	Product_7	FALSE
2794	9310052415204	Actual&PredictedRSV$	1kg	Brand_2	Product_8	11165000	Product_8	FALSE
2795	9310052451202	Actual&PredictedRSV$	250g	Brand_3	Product_9	11159000	Product_9	FALSE
2796	9310052551209	Actual&PredictedRSV$	250g	Brand_3	Product_10	11155000	Product_10	FALSE
2797	9310052533205	Actual&PredictedRSV$	500g	Brand_3	Product_11	11156000	Product_11	FALSE
2798	9310052433253	Actual&PredictedRSV$	500g	Brand_3	Product_12	11176000	Product_12	FALSE
2799	9310264911099	Actual&PredictedRSV$	500g	Brand_4	Product_13	11175001	Product_13	FALSE
2800	9310052433208	Actual&PredictedRSV$	500g	Brand_5	Product_14	11169001	Product_14	FALSE
2801	9414832141502	Actual&PredictedRSV$	125g	Brand_6	Product_15	27244001	Product_15	$-
2802	9414832191026	Actual&PredictedRSV$	125g	Brand_7	Product_16	27285001	Product_16	$-
2803	9414832260036	Actual&PredictedRSV$	500g	Brand_8	Product_17	27908001	Product_17	$-
2804	9414832111802	Actual&PredictedRSV$	250g	Brand_9	Product_18	27250001	Product_18	-
Model Summary Sheet

 

[agerrard]

hi markoakes,

just read your second post. its bizarre that i get values from the actual scan data sheet using the formula before i modified it. so i know that that vlook up was fine. its just getting rid of those darn errors. when i used your one it was great for the cells that had errors but it gave me those false returns for the cells that did have data.

so is it the order of the formula? or is there something else? im completely stumped