Vlookup Multi-Conditions

[Lexman12]

I have a rather large forcaster and am struggling to pull matching information from a second sheet that matches two conditions. I'm trying to match a barcode (A11) and a store code (L11) to bring across an average sales figure that is pulled from avesales.xlsx. I also need to make sure that any erros, whether it be divide by 0, no result or wahtever to disappear (which i can manage with the if function).. I'll post a small example below, if you need further information please fell free to ask and I'll anwser as best I can...

Forecaster.xlsx

---------------------------------------------------------------------------

(A) (L) (M)

(10) Barcode Store Number ?Ave Sale?
(11) 123456 10
(12) 125874 10
(13) 986532 10

----------------------------------------------------------------------------

avesales.xlsx
----------------------------------------------------------------------------

(A) (L) (AE)

(10) Barcode Store Number Ave Sale
(11) 123456 10 14.85
(12) 125874 10 5.85
(13) 986532 10 0
(14) 123456 15 #N/A
(15) 125874 15 3.89
(16) 986532 18 1.84
(17) 123456 24 4.89
(18) 125874 48 17.59
(19) 986532 75 4.78
----------------------------------------------------------------------------

Hope that helps you guys help me just a little.

Cheers,
Lexman12

 

[Haseeb Avarakkan]

Hello Laxman,

Is Forecaster & avesales are two different workbooks or different sheets in a same workbook? Assume these are different workbooks. If so,

Try this Array Formula, Confirmed with Control+Shift+Enter.

=IFERROR(INDEX([avesales.xlsx]Sheet1!$AE$10:$AE$19,MATCH(1,IF([avesales.xlsx]Sheet1!$A$10:$A$19=$A11,IF([avesales.xlsx]Sheet1!$L$10:$L$19=$L11,1)),0)),"")

If it is two different sheets,

=INDEX(avesales!$AE$10:$AE$19,MATCH(1,IF(avesales!$A$10:$A$19=$A11,IF(avesales!$L$10:$L$19=$L11,1)),0))

Confirmed with Control+Shift+Enter.

Also, try

=SUMIFS(avesales!$AE$11:$AE$17,avesales!$A$11:$A$17,$A11,avesales!$L$11:$L$17,$L11)

 

[Lexman12]

Thanks for the reply... it doesn't seem to work, but since i posted they've changed file names apparently because it suits them better.. i tried substituting the file names and excel informed me that I'm an idiot because I done something wrong...

Yes, both sheets are different workbooks.

the forecaster has been changed to Lolliland 1st Month - Cas.xls
the avesales has been changed to Average Sales.xls

The Average sales is from A11 to AE920 and contains 26 different stores. They are sorted to be together. AE contains the figures I need, A and L contain the cells i need to match with cells A and L in the Forecaster

I managed to get my result but i needed to copy and paste just the store into a different spreadsheet on the same workbook. This ruins the whole point of the reason for the forecaster running off reports

=IF(ISNA(VLOOKUP(A17,'Ave Sales'!$A$1:$B$21,2,0)),"",VLOOKUP(A17,'Ave Sales'!$A$1:$B$21,2,0))

if this doesn't help much, I'll manage a screen shot and show you want i need and what i want....

Cheers for the fast reply aswell,
Lexman12

 

[Lexman12]

Also, the sheet on Average Sales is Average Sales, the sheet on the forecaster is First Month

 

[Haseeb Avarakkan]

Open your both files & activate forecater.

M11 enter the formula,

=INDEX(Activate Average Sale & select AE11:AE920,MATCH(1,IF(Activate Average Sales & select A11:A920=A11,IF(Activate Average Sales & select L11:L920=L11,1)),0))

Hit Control+Shift+Enter.

A11 = Barcode
L11 = Store #

Then close the average sales file.

What version of excel are you using?

 

[Lexman12]

This still doesn't give me anything except #N/A. Are there any other methods available to complete this task.

I'm using 2003 Excel.

 

[Haseeb Avarakkan]

You need to Confirm the formula with Control+Shift+Enter, rather than Enter

If you still get #N/A MATCH couldn't found a lookup_values, Barcode & Store #.

Copy any of the Barcode & store # wich give #N/A in to Avg Sales sheet then use this formula.

=ISNUMBER(MATCH(1,IF(A11:A920=A1,IF(L11:L920=A2,1)),0))

Confirmed with Control+Shift+Enter, rather than Enter.

A1 = Bar code
A2 = Store #

What is the answer TRUE or FALSE. If it is FALSE may be your data is looks like same but probably may have extra spaces or something in A1, A2 or A11:A920, L11:L920.

Are there any other methods available to complete this task

I am sorry. I don't know. May be some one could help you.

 

[Lexman12]

It is giving me a False Read... But there is one true read. Would it change much if i reformated everything to be a number etc?

 

[snoopyhr]

try this approach see if it get you started,

Excel Workbook
	A	B	C	D	E	F	G
1	Forecaster.xlsx
2	(A) (L) (M)*
3		Barcode	Store Number	Ave Sale?
4		123456	10*	14.85
5		125874	10*	5.85
6	-13	986532	75	4.78
7	avesales.xlsx
8	(A) (L) (AE)*
9		Barcode	Store Number	Ave Sale
10		123456	10	14.85
11		125874	10	5.85
12		986532	10	0
13		123456	15	#N/A
14		125874	15	3.89
15		986532	18	1.84
16		123456	24	4.89
17		125874	48	17.59
18		986532	75	4.78
19	B8&"-"&LEFT(C8,2)		this refers to sheetForecaster.xlsx
20
21	Noticed your data in sheet Forecaster.xlsx column for store number are not all numbers, I assumed your store number are always 2 digits regardless of entry (text / or number
22
23	$B$20:$B$28&"-"&$C$20:$C$28
24	This part needs to be referenced to the sheet avesales.xlsx
25	Adjust the range as needed.
Sheet6