Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup fuction
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup fuction
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup fuction
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 +-------------------------------------------------------------------+ |Filename: multiple match output.jpg | |Download: http://www.excelforum.com/attachment.php?postid=4947 | +-------------------------------------------------------------------+ -- Nav ------------------------------------------------------------------------ Nav's Profile: http://www.excelforum.com/member.php...o&userid=35831 View this thread: http://www.excelforum.com/showthread...hreadid=556118 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup fuction
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup fuction
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |