Advanced Lookup?? 2

[mattalin]

I have an extremely large amount of data and I need to use some function of Vlookup or similar.

Here's an example, I would like to "lookup" both the "WBS" and "Description" columns based on 2 criteria and display information in the "Finish" column. Let's say I have the following table (simplified of course):

WBS Description Start Finish
.01.03.02.03.02 TEE Design and selection 03/03/03 05/28/03
.01.03.02.03.02 Assembly design/drawing 04/14/03 07/05/03
.01.03.02.03.02 Cooling system design/documentation 05/26/03 08/12/03
.01.03.02.03.02 Isolator dynamic analysis 07/07/03 09/19/03
.01.03.02.03.02 Thermal analysis 08/18/03 10/27/03
.01.03.02.03.02 Receive Prototypes 09/29/03 12/04/03
.01.03.02.03.02 Mechanical integration/checkout 11/10/03 01/11/04
.01.03.02.03.02 Drawing update and release 12/22/03 02/18/04
.01.03.02.03.03 Assembly design/drawing 03/03/03 05/30/03
.01.03.02.03.03 Chassis selection/altered item drawings 03/03/03 05/30/03
.01.03.02.03.03 Cable/Harness drawings 04/30/03 06/27/03
.01.03.02.03.03 Structural analysis 04/01/03 06/30/03
.01.03.02.03.03 Thermal analysis 04/01/03 06/30/03
.01.03.02.03.03 Receive Prototype 08/15/03 08/15/03
.01.03.02.03.03 Mechanical integration/checkout 08/15/03 08/28/03
.01.03.02.03.03 Drawing update and release 08/29/03 09/29/03


I need to set up an equation that will lookup the "WBS" column for say ".01.03.02.03.03" AND then find "Thermal analysis" on the same row and return the "Finish" date on the same row - in this example, I want the Finish date to be "06/30/03"

I have been struggling at this for the longest time. Every time I try to do this with vlookup I always get the date on the first row of ".01.03.02.03.03" (5/30/03) If I try an AND statement I can never get the 2 conditions to work correctly. Does Excel allow one to lookup multiple conditions on the same row and return data from a different column in that row??

Any help would be greatly appreciated!

 

[DaleWatson123321]

I believe I can help. It appears from your example that Excel's "database" functions can be quite effective. These functions are an EXTREMELY powerful component of Excel.

Unfortunately, however, Microsoft's support for these functions is something less than adequate.

If you'd like assistance, email me and I'll then provide you with a solution.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[plantj]

I'm sure Dale can help you but another thought is to create a new column containing the concatenation of WBS and Description to simplify your vlookup.

 

[PepeLeMokko]

When using the AND function did you use the correct syntax which is NOT "event1 AND event2" but "AND ( event1,event2)" ?

 

[xlbo]

plantj's suggstion would be the easiest to implement
Just creaet a new field (to the left of your current data)
that has the formula
=B2&C2 (assuming WBS in colB and description in colC after inserting a column)
copy this formula down to the bottom of your dataset

Your lookup formula would then be
=vlookup(A2&B2,'sheet2!'$A$2:$E$1000,5,false)
given that the summary table you are creating would be in sheet1 and the base data in sheet2, with WBS in colA and description in colB - in sheet1) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[mattalin]

Dale,

Appreciate your help with my problem. The VBA solution, including Excel's database extraction option is exactly what I was looking for.

The concatenation is another good suggestion that would work on other applications I have in the future.

Thanks everyone.

Matt