Multiple condition lookup

Posted by mjmeans on March 01, 2001 5:23 PM

1. I have an unsorted table containing columns for date, person, license, and location.
2. For each person there are licenses numbering 1 through whatever.
3. There is only one entry per date/person/license combination. IOW, date&person&license is always unique.
4. I want to lookup the location value of a record with a specific person and license whos date is within a specific range of dates.
5. The data table cannot be changed to add a column (on any worksheet) containing concatenations of date&person&license to facilitate ordenary lookup or vlookup functions to lookup on a single cell reference.
6. When a lookup yields more than one result, I want the result corresponding to the largest date.
7. When a lookup yields no results, then I want a BLANK cell.

Posted by cpod on March 01, 2001 7:53 PM

Table
Date - Column A
Name - Column B
License - Column C
Location - Column D

Criteria
Date from - F2
Date to - G2
Name - H2
License - I2

=IF(ISNA(INDEX(D2:D7,MATCH(MAX(((A2:A7>=F2)*(A2:A7&LT;=G2)*(B2:B7=H2)*(C2:C7=I2))*(A2:A7)),A2:A7,0),1)),"",INDEX(D2:D7,MATCH(MAX(((A2:A7>=F2)*(A2:A7&LT;=G2)*(B2:B7=H2)*(C2:C7=I2))*(A2:A7)),A2:A7,0),1))

Posted by mjmeans on March 02, 2001 9:16 AM

Thank you. Thanks you. Thank you. Thank you. Thank you.

I figgured out that it neede to be entered as an array formula. Works great. Where can I get detailed information about how to write array formulas. The help text with Excel is very lacking.

Posted by cpod on March 02, 2001 9:30 AM

Apology

I try to be careful about indicating when a formula is an array formula. I apologize.

The MrExcel website has some examples - he calls them CSE formulas.

Posted by mjmeans on March 02, 2001 9:48 AM

I found an error in a special case


When	Who	What	Where
1/2/01	MJM	CPU1	Room1
1/2/01	MJM	CPU2	Room2
1/3/01	RAM	CPU1	Room3
1/6/01	MJM	CPU2	Room1
1/7/01	RAM	CPU1	Room2
1/9/01	JJB	CPU1	Room4
1/9/01	MJM	CPU1	Room4
1/5/01	GRG	CPU1	Room1
1/4/01	GRG	CPU1	Room2
1/3/01	GRG	CPU1	Room3
1/10/01	RAM	CPU1	Room1

In the above data set, the criteria:


From	To	Who	What
1/1/01	1/10/01	MJM	CPU2

Returns Room1 instead of Room2.

Posted by cpod on March 02, 2001 9:56 AM

Re: I found an error in a special case

I thought you said that in a case like this you wanted the most recent date?

Posted by mjmeans on March 02, 2001 10:03 AM

Re: I found an error in a special case

Oops, I meant to post:
From To Who What
1/1/01 1/5/01 MJM CPU2
Results in Room1 instead of Room2.

Posted by cpod on March 02, 2001 10:26 AM

Re: I found an error in a special case

Do you always want to return the max value for what in a case like this?

Posted by mjmeans on March 02, 2001 10:32 AM

Re: I found an error in a special case

Always the max When for all matching Who and What when When is within From and To.
I have a sample spreadsheet made up that demonstrated the problem. To email me add @ and the domain goodnet.com to my name above and I will send the file to you.

Posted by cpod on March 02, 2001 11:07 AM

Re: I found an error in a special case

There is a flaw in my logic. Let me work on it.

Posted by cpod on March 02, 2001 11:19 AM

Re: I found an error in a special case

try this:
=IF(ISNA(INDEX(D2:D12,MATCH(MAX(((A2:A7>=F2)*(A2:A7&LT;=G2)*(B2:B7=H2)*(C2:C7=I2))*(A2:A7)),(A2:A12=MAX(((A2:A7>=F2)*(A2:A7&LT;=G2)*(B2:B7=H2)*(C2:C7=I2))*(A2:A7)))*(B2:B12=H2)*(C2:C12=I2)*(A2:A12),0),1)),"",INDEX(D2:D12,MATCH(MAX(((A2:A7>=F2)*(A2:A7&LT;=G2)*(B2:B7=H2)*(C2:C7=I2))*(A2:A7)),(A2:A12=MAX(((A2:A7>=F2)*(A2:A7&LT;=G2)*(B2:B7=H2)*(C2:C7=I2))*(A2:A7)))*(B2:B12=H2)*(C2:C12=I2)*(A2:A12),0),1))

Posted by mjmeans on March 02, 2001 1:47 PM

Re: I found an error in a special case

That didnt work either. Paste this in a new Sheet 1 and take a look. Replace the right most column in each table with the appropriate formula:
-----------------------------------

When Who What Where PrevWhere
1/2/2001 MJM CPU1 Room1 Room5
1/2/2001 MJM CPU2 Room2 #N/A
1/3/2001 RAM CPU1 Room3 #N/A
1/6/2001 MJM CPU2 Room3 Room1 &LT;- error. expected Room2, data it is returning the first found 1/2/01 record instead of the one matching Who and What
1/7/2001 RAM CPU1 Room2 Room3
1/9/2001 JJB CPU1 Room4 #N/A
1/9/2001 MJM CPU1 Room4 Room1
1/5/2001 GRG CPU1 Room1 Room2
1/4/2001 GRG CPU1 Room2 Room3
1/3/2001 GRG CPU1 Room3 #N/A
1/1/2001 MJM CPU1 Room5 #N/A
1/10/2001 RAM CPU1 Room1 Room2

Search with date ranges
From To Who What Result
1/2/2001 1/5/2001 MJM CPU1 Room1
1/2/2001 1/5/2001 MJM CPU2 Room1 &LT;- error. expected Room2, data it is returning the first found 1/2/01 record instead of the one matching Who and What
1/2/2001 1/5/2001 RAM CPU1 Room3
1/2/2001 1/5/2001 JJB CPU1 #N/A
1/2/2001 1/5/2001 GRG CPU1 Room1
1/2/2001 1/5/2001 GRG CPU2 #N/A

Search dates previous to
To Who What Result
1/5/2001 MJM CPU1 Room1
1/5/2001 MJM CPU2 Room1 &LT;- error. expected Room2, data it is returning the first found 1/2/01 record instead of the one matching Who and What
1/5/2001 RAM CPU1 Room3
1/5/2001 JJB CPU1 #N/A
1/5/2001 GRG CPU1 Room2
1/5/2001 GRG CPU2 #N/A

Search of all dates
Who What Result
MJM CPU1 Room4
MJM CPU2 Room3
RAM CPU1 Room1
JJB CPU1 Room4
GRG CPU1 Room1
GRG CPU2 #N/A

----------------------------------
=IF(ISNA(INDEX(Where,MATCH(MAX(((When&LT;A2)*(Who=B2)*(What=C2))*(When)),When,0),1)),#N/A,INDEX(Where,MATCH(MAX(((When&LT;A2)*(Who=B2)*(What=C2))*(When)),When,0),1))
=IF(ISNA(INDEX(Where,MATCH(MAX(((When>=A17)*(When&LT;=B17)*(Who=C17)*(What=D17))*(When)),When,0),1)),#N/A,INDEX(Where,MATCH(MAX(((When>=A17)*(When&LT;=B17)*(Who=C17)*(What=D17))*(When)),When,0),1))
=IF(ISNA(INDEX(Where,MATCH(MAX(((When&LT;A26)*(Who=B26)*(What=C26))*(When)),When,0),1)),#N/A,INDEX(Where,MATCH(MAX(((When&LT;A26)*(Who=B26)*(What=C26))*(When)),When,0),1))
=IF(ISNA(INDEX(Where,MATCH(MAX(((Who=A35)*(What=B35))*(When)),When,0),1)),#N/A,INDEX(Where,MATCH(MAX(((Who=A35)*(What=B35))*(When)),When,0),1))

Posted by Aladin Akyurek on March 03, 2001 2:51 AM

Re: I found an error in a special case

I DIIDN'T FOLLOW THE THREAD VERY CLOSELY, SO I APOLOGIZE IF THE FOLLOWING IS BESIDE THE POINT.

Array-enter

=INDIRECT(ADDRESS(MAX(1*(A2:A12>=E2)*(A2:A12&LT;=F2)*(B2:B12=G2)*(C2:C12=H2)*ROW(A2:A12)),COLUMN(A2:A12)+3))

where A2:A12 is the range of WHEN values, B2:B12 the range of WHO values (MJM, etc.), C2:C12 the range of WHAT values (CPU1, etc.) and D2:D12 the range of WHERE values (Room1, etc.)

E2 contains the lower WHEN crit (from-value), F2 the upper WHEN crit (to-value), G2 the WHO crit, and H2 the WHAT crit.

If you insist catching error values, array-enter instead:

=IF(ISNA(VLOOKUP(G2,B2:B12,1,0)),"",IF(ISNA(VLOOKUP(H2,C2:C12,1,0)),"",INDIRECT(ADDRESS(MAX(1*(A2:A12>=E2)*(A2:A12&LT;=F2)*(B2:B12=G2)*(C2:C12=H2)*ROW(A2:A12)),COLUMN(A2:A12)+3))))

If any good, cpod should also get credit as much as me or even more.

Aladin