Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria for lookup array
G'day there everyone,
not sure if this is possible. I understand the row v column principal for a lookup array: Ideally what I need is this A B C D E 1 From To Company Size Cost 2 A B ABC Trans 36 =AGREED 3 A B XYZ Trans 36 =LOOKUP If (C2="","",IF(C2="ABC Trans","AGREED",OFFSET(INFO!($C2,MATCH('Cost','Fro m=A2' AND 'To=B2' AND 'Size=D2))))) Now this formula is totally improvised and I know I have absolutely no where near enough knowledge to make it work, hoping someone out there may be able to point me in the right direction. A huge TIA Regards Mark. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria for lookup array
I'm unsure what exactly you want to see in the lookup result. Do you want, if
company is not ABC, to see the text: From A To B Size 36 ? If so, in the Else portion of your if statement enter ,"From = "&A2&" To= "&B2&" Size = "&C2 Otherwise, could I get a better idea of your expected result? "NoodNutt" wrote: G'day there everyone, not sure if this is possible. I understand the row v column principal for a lookup array: Ideally what I need is this A B C D E 1 From To Company Size Cost 2 A B ABC Trans 36 =AGREED 3 A B XYZ Trans 36 =LOOKUP If (C2="","",IF(C2="ABC Trans","AGREED",OFFSET(INFO!($C2,MATCH('Cost','Fro m=A2' AND 'To=B2' AND 'Size=D2))))) Now this formula is totally improvised and I know I have absolutely no where near enough knowledge to make it work, hoping someone out there may be able to point me in the right direction. A huge TIA Regards Mark. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria for lookup array
G'day Sean
thx for your reply clarification: the company I work for utilises company drivers (ABC Trans) and contractors. lets assume the driver is travelling from Point A to Point B with "36" pallets of Glider Engines. Now if that driver works for "ABC Trans" then the "Cost" is fixed, so it ="AGREED", but if it is any other contractor, then I need it to look up the "Cost" form the Lookup Array matching the 3-way criteria of "From","To" & "Size" Every load "From" & "To" &"Size" will be different depending on customer's transport requirement. It doesn't matter who the contractor is as all outside contractors get paid the same regardless. Hope this explains it clearer for you. I currently have a simple lookup array from a previous company I worked for which essentially just cross matched Customer(Row) with VehicleSize(Column) and returned the intersecting value, this array is slightly different as it contains a From(Row) + To(Row) with LoadSize(Column). haven't done one of these before hence the call for help. Many thx Mark. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria for lookup array
G'day again Sean.
decided to simplify the whole thing by allocating each :From" & "To" with a code # eg A to B = 31, A to C = 32. For the moment it is working well. thx again for your input. Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MODE fx in array using multiple criteria | Excel Worksheet Functions | |||
Can I use an array formula with multiple criteria in the same row? | Excel Worksheet Functions | |||
Array Formulas with multiple criteria in the same row? | Excel Worksheet Functions | |||
SUMIF MULTIPLE ARRAY CRITERIA | Excel Worksheet Functions | |||
Array Formula w/ Multiple SumIf Criteria | Excel Worksheet Functions |