Great! Many thanks. This works. I guess I just had an error when I entered
the variables and didn't realize it. Is there a way that I can have it return
"unavailable" instead of #N/A if there is an invalid combination of variables
entered?
--
dat842


"T. Valko" wrote:

Here's a small sample file that demonstrates this. I included a second
formula.

multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb

http://cjoint.com/?cygfIVADn7

As you'll see, both formulas return the correct result. If you get #N/A with
the array formula that means something isn't matching. There are many
possible reasons.

You may have unseen whitespace characters in your data.

The dates may not be true Excel dates.

The numeric values might actually be TEXT numbers. TEXT numbers and numeric
numbers are not the same.

So, you'll have to troubleshoot for those possibilities.

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks. I tried your suggestion, but am still getting a #N/A error. I have
entered the formula as an array. Any ideas what I'm missing?
--
dat842


"T. Valko" wrote:

One way...

Array entered** :

=INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...

I'm trying to use the match function with a nested if statement. Is
this
possible?

My table looks like the following.

Column
D E F G H
UNIT TERM STARTDATE ENDDATE RATE
21843 2 2/1/2009 2/7/2009 1350
21843 3 2/1/2009 2/7/2009 1040
21843 4 2/1/2009 2/7/2009 1015
21843 2 2/8/2009 2/14/2009 1350
21843 3 2/8/2009 2/14/2009 1040
21843 4 2/8/2009 2/14/2009 1015
19608 2 3/1/2009 3/7/2009 2055
19608 3 3/8/2009 3/15/2009 1725
19608 4 3/22/2009 3/29/2009 1670


I need the user to enter a value for the unit, term and date and have
excel
return the matching value in the rate column. The problem is that the
date
which is entered by the user will be inbetween the StartDate and
EndDate
values which are in the table. I'm trying to use the match function
with a
nested if statement to determine if the date is within the StartDate
and
EndDate range. If it is, the match function should use the date in the
StartDate column.

For example,

User enters a unit in cell A5 = 19608

User enters a date in cell A9 3/10/2009

Term is hard coded as a value 3.

Using the table above, I'd like excel to return the value 1725

My formula is

=INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10
=IF(AND($A$9=F3, $A$9<=G3),0,F3)),0))

However, my formula doesn't work. Any ideas would be appreciated.
--
dat842


.



.