Index / Match problem

[volkl77]

I am using the index / match functions to search a range of data for store codes and sales person codes as below.

=INDEX($A$8:$F$33,MATCH($B34,$B$8:$B$33,0),MATCH($C34,$C$8:$C$33,0),MATCH($F$8,$A$8:$F$8,0))

I am running into an issue where because there are multiple employees working in the same store when it tries to lookup the store code i am getting a reference error, at least I think this is what is happening. Here is a brief snapshot of what i am talking about. The first column being the employee code (column B) the second the store code (column C).

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64" span="2"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt;width:48pt" width="64" height="17">1000</td> <td class="xl70" style="border-left:none;width:48pt" width="64">01</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17">1003</td> <td class="xl71" style="border-top:none;border-left:none">01</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17">1007</td> <td class="xl71" style="border-top:none;border-left:none">01</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17">1009</td> <td class="xl71" style="border-top:none;border-left:none">01</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17">1022</td> <td class="xl71" style="border-top:none;border-left:none">01</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17">1024</td> <td class="xl71" style="border-top:none;border-left:none">01</td> </tr> </tbody></table>
any help would be appreciated

 

[Aladin Akyurek]

Care to describe the task you want to do in terms of the sample you posted?

 

[volkl77]

Basically each day i get a list of employee codes (column B) their store code (column C), and their total sales. Because there are hundreds of empolyee's the owners would like a quick way to find an employee's sales by entering their store # and employee #. So i was just going to use a index / match to create a line at the top where they can just type in these values and it will show them without having to search manually.

 

[T. Valko]

Basically each day i get a list of employee codes (column B) their store code (column C), and their total sales. Because there are hundreds of empolyee's the owners would like a quick way to find an employee's sales by entering their store # and employee #. So i was just going to use a index / match to create a line at the top where they can just type in these values and it will show them without having to search manually.

Try this...

Book1
	A	B	C	D	E	F	G
1	Employee	Store	Sales	_	Employee	Store	Sales
2	1000	01	54	_	1007	01	39
3	1003	01	18	_	_	_	_
4	1007	01	39	_	_	_	_
5	1009	01	62	_	_	_	_
6	1022	01	55	_	_	_	_
7	1024	01	68	_	_	_	_
Sheet1

This array formula** entered in G2:

=INDEX(C2:C7,MATCH(1,IF(A2:A7=E2,IF(B2:B7=F2,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[volkl77]

This seems to be working except when the store # is not equal to 01. There is also stores 02, 03, 09. Thanks again for the help

 

[volkl77]

Disregard, i had an extra $

 

[T. Valko]

Disregard, i had an extra $

Good deal. Thanks for the feedback!