=INDEX($A$3:$J$5,MATCH(D10,$A$3:$A$5,0),(MATCH(C10 ,{"A","B","C"},0)-1)*3+1+MATCH(B10,{"A","B","C"},0))
In the example above:
$A$3:$J$5=your data table i.e A 10 20 30 15
25 35 etc
B10= Code for zone i.e. A,B,C
C10= Code for Service Type i.e. A, B or C
D10=Box type e.g A
Formula assumes 3 zones per Service Type
In your example, I believe result should be 35 not 70.
HTH
"Nav" wrote:
Dear helper.
How would I do this?
I have 2 tables.
TABLE 1,
Service Type A Service Type B Service
Type C
boxtype |zoneA|zoneB|zoneC |zoneA|zoneB|zoneC |zoneA|zoneB|zoneC
A 10 20 30 15 25 35
20 30 70
TABLE 2,
Province | Zone
ABC A
ABD B
ABE A
ABF C
Now my report is.
I can get the boxtype, Service Type, Province.
Example
if I get,
A (boxsize) | Service Type (B) | ABF (province - zone C)
if all the criteria are match, then show the numbers (70)
How do I create functions for this, pleae help.
Thx,
--
Nav
------------------------------------------------------------------------
Nav's Profile: http://www.excelforum.com/member.php...o&userid=35831
View this thread: http://www.excelforum.com/showthread...hreadid=556118