Lookup Help very urgent

[ashutoshn11]

Team,

Kindly help me with lookup function or given data set, I tried all the possible lookup function but none are working.

[pre]

Client	Rep	Jan	Feb	Mar	Apr	May	Jun	Jul
8	111	80107	36765	70126	35153	47821	54018	10927
5	222	17937	79881	37221	86430	42982	80197	10435
4	333	26189	66380	58051	71108	41813	54377	57169
1	444	58595	99600	81524	21072	37469	30489	99734
3	555	29068	60221	91618	74770	48555	44790	33882
7	666	79786	42104	79428	40285	12982	22618	37308
6	777	60459	44996	74136	32376	21796	16825	35416
5	888	44054	41168	40214	94249	83524	81686	97657
3	999	14176	36653	93238	74750	77539	47395	92221
5	1111	73383	95754	33833	64998	12623	72618	36163
5	2222	25173	30726	90335	70279	35436	77746	91000
2	3333	62192	47383	69536	60354	60771	41244	63955
1	4444	33515	67125	97996	29504	62126	91295	92173
5	5555	85827	75365	59124	94429	53355	46828	17595
5	6666	69482	15261	96956	55397	89556	40647	26711
6	7777	83347	29781	97340	52553	88550	98632	40217
1	8888	81738	16689	67752	18505	17292	33886	41980
3	9999	13698	58740	42068	86389	41060	21109	84640
Client	Rep	May	Feb	Mar	Jul	Jan	Apr	Jun
1	444	???
2	3333	???
3	999	???
4	333	???
5	5555	???
6	777	???
7	666	???
8	111	???

[/pre]

 

[Faseeh]

Hi ashutoshn11,


Welcome to the forums, with your data in A1:I19 and output in L1:T9 enter following in M2 and drag to right and down:

[pre]

=SUMPRODUCT(($A$2:$A$19=$L2)*($B$2:$B$19=$M2)*($C$1:$I$1=N$1)*($C$2:$I$19))

Here is the output:


Row#    L       M       N       O       P       Q       R       S       T
1	Client	Rep	May	Feb	Mar	Jul	Jan	Apr	Jun
2	1	444	37469	99600	81524	99734	58595	21072	30489
3	2	3333	60771	47383	69536	63955	62192	60354	41244
4	3	999	77539	36653	93238	92221	14176	74750	47395
5	4	333	41813	66380	58051	57169	26189	71108	54377
6	5	5555	53355	75365	59124	17595	85827	94429	46828
7	6	777	21796	44996	74136	35416	60459	32376	16825
8	7	666	12982	42104	79428	37308	79786	40285	22618
9	8	111	47821	36765	70126	10927	80107	35153	54018

[/pre]
Regards,

 

[ashutoshn11]

Hi Faseesh,


Thanx for the quick response? Could you please elaborate which cell l2,M2, N1 refering to?


Sorry for basic question I am trying to learn advance excel.

 

[Faseeh]

Hi ashutoshn11,


Refer to my last post i just edited it to show you the columns and rows i refereed to.


L2 is Client,

M2 is Rep,

Ni is May,


When you drag the formula to right, due to cell referring i used, May will change to Feb, Mar etc. When you drag it down Client and Rep will change. Hence this formula works.


Regards,

 

[ashutoshn11]

Hi Fasheesh,


Its working.


Thanx for help.


Much appreciated.

 

[Faseeh]

You're welcome!


Faseeh