Vlookup fuction: How would I do this?

[Nav]

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,

[Toppers]

=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
>
>

[Nav]

Thank you for your help topper. I believe I am getting close.

I still dont get the correct output, also, can you explain me the *3+1+match

I believe *3 is the 3 zone for each service type or I may be wrong.

Please help.
I attached the jpg file in this reply

[Nav]

Dear Topper,

I think I get it now. Thankx for your help.

I have attached the jpeg file on the formula.

instead of +1, I replaced it with -2. Then, the result was perfect.

Without your help, this will never happen to me.

Thx a lot,

[Toppers]

Nav,
Glad it's done the trick and thank you for the feedback.

"Nav" wrote:

>
> Dear Topper,
>
> I think I get it now. Thankx for your help.
>
> I have attached the jpeg file on the formula.
>
> instead of +1, I replaced it with -2. Then, the result was perfect.
>
> Without your help, this will never happen to me.
>
> Thx a lot,
>
>
> +-------------------------------------------------------------------+
> |Filename: multiple match output 2.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=4948 |
> +-------------------------------------------------------------------+
>
> --
> Nav
> ------------------------------------------------------------------------
> Nav's Profile: http://www.excelforum.com/member.php...o&userid=35831
> View this thread: http://www.excelforum.com/showthread...hreadid=556118
>
>