Multiple Criteria for lookup array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NoodNutt - 08 Mar 2008 12:17 GMT

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','From=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.

Reply to this Message

Sean Timmons - 08 Mar 2008 19:16 GMT

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?

> G'day there everyone,
>
[quoted text clipped - 20 lines]
> Regards
> Mark.

Reply to this Message

NoodNutt - 10 Mar 2008 02:41 GMT

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.

Reply to this Message

NoodNutt - 10 Mar 2008 04:19 GMT

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 to this Message