Help: Excel lookup with multiple conditions

[leekb]

Dear experts,

Appreciate if you could help me to devise a formula to lookup a table with multiple matches using three columns three columns:

Example

<A> <B> <C> <D>
<1> date time name value
<2> 01-12-02 00:00 Apple (Excel formula?)
<3> 01-12-02 01:00 Apple (Excel formula?)
<4> 01-12-02 02:00 Apple (Excel formula?)
<5> 01-12-02 03:00 Apple (Excel formula?)
<6> 01-12-02 00:00 Pear (Excel formula?)
<7> 01-12-02 01:00 Pear (Excel formula?)
...
...

The Lookup Table
----------------
<W> <X> <Y> <Z>
<1> date time name value
<2> 01-12-02 01:00 Pear 834754
<3> 01-12-02 00:00 Apple 38475
<4> 01-12-02 01:00 Apple 456459
<5> 01-12-02 00:00 Pear 345656
<6> 01-12-02 02:00 Apple 454445
<7> 01-12-02 03:00 Apple 456453

thanks in advance.

 

[Digga]

Hi leekb,

you could concatenate the three columns in both tables, sort both tables by the concatenated columns and then do a vlookup.

Digga

Sharing Knowledge Saves Valuable Time!

 

[Draig]

Hi leekb!

One way to achieve what you want is to use a function like DMAX(), but this would involve your setting up a separate Criteria Area for each formula.
I'll work on this one, but meanwhile, some else may come up with a better solution.

Barborne
Worcester
UK

 

[leekb]

I found the answer just a moment ago. This can be achieved using an array formula.

{=INDEX($Z$2:$Z$7,MATCH(A2&B2&C2,$W$2:$W$7&$X$2:$X$7&$Y$2:$Y$7,0))}

thanks everyone.