If & Index & Match
 

what is wrong with the below function:

=IF(I161500000,(INDEX(C10:H11,MATCH(I17,B10:B11,0 ),MATCH(I18,C5:H5,1),1))),IF(I161000000&I1615000 00,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5, 1),1)),IF(I16650000&I161000000,INDEX(C6:H7,MATCH (I17,B6:B7,1),MATCH(I18,C5:H5,1),1))

If & Index & Match
 

would it be better to get the value by running it in VBA? if so, how do
you write it?
Please help :*(

wrote:
what is wrong with the below function:

=IF(I161500000,(INDEX(C10:H11,MATCH(I17,B10:B11,0 ),MATCH(I18,C5:H5,1),1))),IF(I161000000&I1615000 00,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5, 1),1)),IF(I16650000&I161000000,INDEX(C6:H7,MATCH (I17,B6:B7,1),MATCH(I18,C5:H5,1),1))

If & Index & Match
 

Hazarding some thoughts. It's tough to figure out what you're trying to do,
as it stands ..

=IF(I161500000,(INDEX(C10:H11,MATCH(I17,B10:B11,0 ),MATCH(I18,C5:H5,1),1))),IF(I161000000&I1615000 00,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5, 1),1)),IF(I16650000&I161000000,INDEX(C6:H7,MATCH (I17,B6:B7,1),MATCH(I18,C5:H5,1),1))

The first IF part
=IF(I161500000,(INDEX(C10:H11,MATCH(I17,B10:B11,0 ),MATCH(I18,C5:H5,1),1))),IF..

should perhaps read as:
=IF(I161500000,INDEX(C10:H11,MATCH(I17,B10:B11,0) ,MATCH(I18,C5:H5,1)),IF..

and the 2nd IF (it stumps me <g):
... IF(I161000000&I161500000,

As it stands the above would either simplify to:
... IF(I161000000

or perhaps you were trying to do something like:
... IF(AND(I161000000,I16<=1500000), ..

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
orig. post:
wrote:
what is wrong with the below function:

=IF(I161500000,(INDEX(C10:H11,MATCH(I17,B10:B11,0 ),MATCH(I18,C5:H5,1),1))),IF(I161000000&I1615000 00,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5, 1),1)),IF(I16650000&I161000000,INDEX(C6:H7,MATCH (I17,B6:B7,1),MATCH(I18,C5:H5,1),1))

If & Index & Match
 

Hi

From the layout of the data you posted when you raised this question
under a separate thread, I posted the following solution to you. It
returned the value of 3, which is what I thought you were seeking.

Did it not work for you?

I'm not sure that I understand what you want, but maybe

=INDEX(D2:I7,MIN(MATCH(B9,A3:A7,1),MATCH(B10,B3:B7 ,1))+1,MATCH(B11,D2:I2,1))


--
Regards

Roger Govier


wrote in message
oups.com...
what is wrong with the below function:

=IF(I161500000,(INDEX(C10:H11,MATCH(I17,B10:B11,0 ),MATCH(I18,C5:H5,1),1))),IF(I161000000&I1615000 00,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5, 1),1)),IF(I16650000&I161000000,INDEX(C6:H7,MATCH (I17,B6:B7,1),MATCH(I18,C5:H5,1),1))

If & Index & Match
 

Hi,

Also you shouldn't have used "&" in your formula as this must be used
for CONCATENATION and not as "and" operator.

Thanks,

Shail


wrote:
what is wrong with the below function:

=IF(I161500000,(INDEX(C10:H11,MATCH(I17,B10:B11,0 ),MATCH(I18,C5:H5,1),1))),IF(I161000000&I1615000 00,INDEX(C8:H9,MATCH(I17,B8:B9,1),MATCH(I18,C5:H5, 1),1)),IF(I16650000&I161000000,INDEX(C6:H7,MATCH (I17,B6:B7,1),MATCH(I18,C5:H5,1),1))