Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if


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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default match function with nested if

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if

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



.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default match function with nested if

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



.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if

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


.



.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if

Another question....are there any limitations that you are aware of when
using these formulas? For example, I've put the data table (about 65000 rows)
on one tab and the lookup formula in another tab. I also have the data ranges
setup to be the entire column on the data tab of the spreadsheet. I can't get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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


.



.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if

Thanks for your help. I've tried both formulas across tabs. Here they are. Do
you see any issues?

{=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))}

=SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H)


--
dat842


"dat842" wrote:

Another question....are there any limitations that you are aware of when
using these formulas? For example, I've put the data table (about 65000 rows)
on one tab and the lookup formula in another tab. I also have the data ranges
setup to be the entire column on the data tab of the spreadsheet. I can't get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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


.



.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default match function with nested if

What version of Excel are you using?

Unless you're using Excel 2007 you *can't* use entire columns as range
references with either an array entered formula or a SUMPRODUCT formula.

Trapping the error will make the formula twice as long (unless you're using
Excel 2007).

Any version of Excel (still array entered):

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

Another consideration: If your data set is ~65,000 rows this error trapped
formulas will be somewhat slow to calculate since it has to calculate twice
if/when there isn't an error. It may be better to use 2 cells, one with the
basic formula and then a smaller formula that tests the cell with the basic
formula. Something like this:

A1 = basic formula (array formula without the error trap). This will return
either the correct number or the #N/A error.

A2: formula

=IF(ISNA(A1),"Unavailable",A1)

Excel 2007 only (still array entered):

=IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable")

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks for your help. I've tried both formulas across tabs. Here they are.
Do
you see any issues?

{=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))}

=SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H)


--
dat842


"dat842" wrote:

Another question....are there any limitations that you are aware of when
using these formulas? For example, I've put the data table (about 65000
rows)
on one tab and the lookup formula in another tab. I also have the data
ranges
setup to be the entire column on the data tab of the spreadsheet. I can't
get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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


.



.



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if

Thanks. I am using Excel 2007. Good tip about the speed issue and the error.
I'm still having trouble getting the formula to work for the entire column.

Here's what I've done. I have the formula setup on one sheet and the small
sample table on the second sheet. The formula contains the ranges for the
sample table. Everything works fine.

Then I grab the entire data set and paste it into sheet two over the top of
the sample data. I adjust the ranges in the formula to reflect the new
dataset and the formula doesn't work. Any ideas?
--
dat842


"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 you *can't* use entire columns as range
references with either an array entered formula or a SUMPRODUCT formula.

Trapping the error will make the formula twice as long (unless you're using
Excel 2007).

Any version of Excel (still array entered):

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

Another consideration: If your data set is ~65,000 rows this error trapped
formulas will be somewhat slow to calculate since it has to calculate twice
if/when there isn't an error. It may be better to use 2 cells, one with the
basic formula and then a smaller formula that tests the cell with the basic
formula. Something like this:

A1 = basic formula (array formula without the error trap). This will return
either the correct number or the #N/A error.

A2: formula

=IF(ISNA(A1),"Unavailable",A1)

Excel 2007 only (still array entered):

=IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable")

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks for your help. I've tried both formulas across tabs. Here they are.
Do
you see any issues?

{=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))}

=SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H)


--
dat842


"dat842" wrote:

Another question....are there any limitations that you are aware of when
using these formulas? For example, I've put the data table (about 65000
rows)
on one tab and the lookup formula in another tab. I also have the data
ranges
setup to be the entire column on the data tab of the spreadsheet. I can't
get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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


.



.



.

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default match function with nested if

Then I grab the entire data set and paste it
into sheet two over the top of the sample data


I'm not following you on that. Why would you do that? What purpose does it
serve?

I adjust the ranges in the formula to reflect
the new dataset and the formula doesn't work.


What does "doesn't work" mean exactly? You get an error? An incorrect
result?

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks. I am using Excel 2007. Good tip about the speed issue and the
error.
I'm still having trouble getting the formula to work for the entire
column.

Here's what I've done. I have the formula setup on one sheet and the
small
sample table on the second sheet. The formula contains the ranges for the
sample table. Everything works fine.

Then I grab the entire data set and paste it into sheet two over the top
of
the sample data. I adjust the ranges in the formula to reflect the new
dataset and the formula doesn't work. Any ideas?
--
dat842


"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 you *can't* use entire columns as range
references with either an array entered formula or a SUMPRODUCT formula.

Trapping the error will make the formula twice as long (unless you're
using
Excel 2007).

Any version of Excel (still array entered):

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

Another consideration: If your data set is ~65,000 rows this error
trapped
formulas will be somewhat slow to calculate since it has to calculate
twice
if/when there isn't an error. It may be better to use 2 cells, one with
the
basic formula and then a smaller formula that tests the cell with the
basic
formula. Something like this:

A1 = basic formula (array formula without the error trap). This will
return
either the correct number or the #N/A error.

A2: formula

=IF(ISNA(A1),"Unavailable",A1)

Excel 2007 only (still array entered):

=IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable")

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks for your help. I've tried both formulas across tabs. Here they
are.
Do
you see any issues?

{=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))}

=SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H)


--
dat842


"dat842" wrote:

Another question....are there any limitations that you are aware of
when
using these formulas? For example, I've put the data table (about
65000
rows)
on one tab and the lookup formula in another tab. I also have the data
ranges
setup to be the entire column on the data tab of the spreadsheet. I
can't
get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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


.



.



.





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if

Then I grab the entire data set and paste it
into sheet two over the top of the sample data


I'm not following you on that. Why would you do that? What purpose does it
serve? I'm adding in the entire data table which I retrieved out of a SQL db onto sheet two. This allows me to test the formula against a small table of data, and then add the full table later.



I adjust the ranges in the formula to reflect
the new dataset and the formula doesn't work.


What does "doesn't work" mean exactly? You get an error? An incorrect
result? Doesn't work means that I get an "#N/A" error. When I know the value I'm looking up exists in the table.



--
dat842


"T. Valko" wrote:

Then I grab the entire data set and paste it
into sheet two over the top of the sample data


I'm not following you on that. Why would you do that? What purpose does it
serve?

I adjust the ranges in the formula to reflect
the new dataset and the formula doesn't work.


What does "doesn't work" mean exactly? You get an error? An incorrect
result?

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks. I am using Excel 2007. Good tip about the speed issue and the
error.
I'm still having trouble getting the formula to work for the entire
column.

Here's what I've done. I have the formula setup on one sheet and the
small
sample table on the second sheet. The formula contains the ranges for the
sample table. Everything works fine.

Then I grab the entire data set and paste it into sheet two over the top
of
the sample data. I adjust the ranges in the formula to reflect the new
dataset and the formula doesn't work. Any ideas?
--
dat842


"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 you *can't* use entire columns as range
references with either an array entered formula or a SUMPRODUCT formula.

Trapping the error will make the formula twice as long (unless you're
using
Excel 2007).

Any version of Excel (still array entered):

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

Another consideration: If your data set is ~65,000 rows this error
trapped
formulas will be somewhat slow to calculate since it has to calculate
twice
if/when there isn't an error. It may be better to use 2 cells, one with
the
basic formula and then a smaller formula that tests the cell with the
basic
formula. Something like this:

A1 = basic formula (array formula without the error trap). This will
return
either the correct number or the #N/A error.

A2: formula

=IF(ISNA(A1),"Unavailable",A1)

Excel 2007 only (still array entered):

=IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable")

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks for your help. I've tried both formulas across tabs. Here they
are.
Do
you see any issues?

{=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))}

=SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H)


--
dat842


"dat842" wrote:

Another question....are there any limitations that you are aware of
when
using these formulas? For example, I've put the data table (about
65000
rows)
on one tab and the lookup formula in another tab. I also have the data
ranges
setup to be the entire column on the data tab of the spreadsheet. I
can't
get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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


.



.



.



.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default match function with nested if

Well, I don't know what to tell you. The formula works as I demonstrated in
the sample file.

Something you're doing with the data manipulation seems to be causing the
problem and I can't duplicate what you're doing or the data you're working
with on my end.

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Then I grab the entire data set and paste it
into sheet two over the top of the sample data


I'm not following you on that. Why would you do that? What purpose does
it
serve? I'm adding in the entire data table which I retrieved out of a SQL
db onto sheet two. This allows me to test the formula against a small
table of data, and then add the full table later.



I adjust the ranges in the formula to reflect
the new dataset and the formula doesn't work.


What does "doesn't work" mean exactly? You get an error? An incorrect
result? Doesn't work means that I get an "#N/A" error. When I know the
value I'm looking up exists in the table.



--
dat842


"T. Valko" wrote:

Then I grab the entire data set and paste it
into sheet two over the top of the sample data


I'm not following you on that. Why would you do that? What purpose does
it
serve?

I adjust the ranges in the formula to reflect
the new dataset and the formula doesn't work.


What does "doesn't work" mean exactly? You get an error? An incorrect
result?

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks. I am using Excel 2007. Good tip about the speed issue and the
error.
I'm still having trouble getting the formula to work for the entire
column.

Here's what I've done. I have the formula setup on one sheet and the
small
sample table on the second sheet. The formula contains the ranges for
the
sample table. Everything works fine.

Then I grab the entire data set and paste it into sheet two over the
top
of
the sample data. I adjust the ranges in the formula to reflect the new
dataset and the formula doesn't work. Any ideas?
--
dat842


"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 you *can't* use entire columns as range
references with either an array entered formula or a SUMPRODUCT
formula.

Trapping the error will make the formula twice as long (unless you're
using
Excel 2007).

Any version of Excel (still array entered):

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

Another consideration: If your data set is ~65,000 rows this error
trapped
formulas will be somewhat slow to calculate since it has to calculate
twice
if/when there isn't an error. It may be better to use 2 cells, one
with
the
basic formula and then a smaller formula that tests the cell with the
basic
formula. Something like this:

A1 = basic formula (array formula without the error trap). This will
return
either the correct number or the #N/A error.

A2: formula

=IF(ISNA(A1),"Unavailable",A1)

Excel 2007 only (still array entered):

=IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable")

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks for your help. I've tried both formulas across tabs. Here
they
are.
Do
you see any issues?

{=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))}

=SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H)


--
dat842


"dat842" wrote:

Another question....are there any limitations that you are aware of
when
using these formulas? For example, I've put the data table (about
65000
rows)
on one tab and the lookup formula in another tab. I also have the
data
ranges
setup to be the entire column on the data tab of the spreadsheet. I
can't
get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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


.



.



.



.



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default match function with nested if

I really appreciate all the help you've provided. I'm still struggling very
much to get this to work. If it is possible for you to take a quick look at
my excel sheet, I would surely appreciate it. If you are willing to do this,
please send me an email to and I'll send you the file.
Many thanks for your help.
--
dat842


"T. Valko" wrote:

Well, I don't know what to tell you. The formula works as I demonstrated in
the sample file.

Something you're doing with the data manipulation seems to be causing the
problem and I can't duplicate what you're doing or the data you're working
with on my end.

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Then I grab the entire data set and paste it
into sheet two over the top of the sample data

I'm not following you on that. Why would you do that? What purpose does
it
serve? I'm adding in the entire data table which I retrieved out of a SQL
db onto sheet two. This allows me to test the formula against a small
table of data, and then add the full table later.



I adjust the ranges in the formula to reflect
the new dataset and the formula doesn't work.

What does "doesn't work" mean exactly? You get an error? An incorrect
result? Doesn't work means that I get an "#N/A" error. When I know the
value I'm looking up exists in the table.



--
dat842


"T. Valko" wrote:

Then I grab the entire data set and paste it
into sheet two over the top of the sample data

I'm not following you on that. Why would you do that? What purpose does
it
serve?

I adjust the ranges in the formula to reflect
the new dataset and the formula doesn't work.

What does "doesn't work" mean exactly? You get an error? An incorrect
result?

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks. I am using Excel 2007. Good tip about the speed issue and the
error.
I'm still having trouble getting the formula to work for the entire
column.

Here's what I've done. I have the formula setup on one sheet and the
small
sample table on the second sheet. The formula contains the ranges for
the
sample table. Everything works fine.

Then I grab the entire data set and paste it into sheet two over the
top
of
the sample data. I adjust the ranges in the formula to reflect the new
dataset and the formula doesn't work. Any ideas?
--
dat842


"T. Valko" wrote:

What version of Excel are you using?

Unless you're using Excel 2007 you *can't* use entire columns as range
references with either an array entered formula or a SUMPRODUCT
formula.

Trapping the error will make the formula twice as long (unless you're
using
Excel 2007).

Any version of Excel (still array entered):

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

Another consideration: If your data set is ~65,000 rows this error
trapped
formulas will be somewhat slow to calculate since it has to calculate
twice
if/when there isn't an error. It may be better to use 2 cells, one
with
the
basic formula and then a smaller formula that tests the cell with the
basic
formula. Something like this:

A1 = basic formula (array formula without the error trap). This will
return
either the correct number or the #N/A error.

A2: formula

=IF(ISNA(A1),"Unavailable",A1)

Excel 2007 only (still array entered):

=IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable")

--
Biff
Microsoft Excel MVP


"dat842" wrote in message
...
Thanks for your help. I've tried both formulas across tabs. Here
they
are.
Do
you see any issues?

{=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))}

=SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H)


--
dat842


"dat842" wrote:

Another question....are there any limitations that you are aware of
when
using these formulas? For example, I've put the data table (about
65000
rows)
on one tab and the lookup formula in another tab. I also have the
data
ranges
setup to be the entire column on the data tab of the spreadsheet. I
can't
get
the formula to work in this situation.
--
dat842


"dat842" wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MYSTERY: NESTED INDEX AND MATCH FUNCTIONS JingleRock[_2_] Excel Worksheet Functions 28 March 14th 09 02:39 AM
nested MATCH within Array formula? katy Excel Worksheet Functions 2 January 23rd 08 02:08 AM
Nested MATCH with two tables Dave Lagergren Excel Worksheet Functions 3 September 22nd 07 12:47 AM
Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH' JT Excel Worksheet Functions 2 February 15th 07 12:46 AM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 10:46 PM


All times are GMT +1. The time now is 11:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"