Match & Index
 

Here's the scenario:

A B C
1 10 0
2 20 1
3 30 2
4 40 3

I want to index column C then match column A & B with criteria on another
worksheet. However the criteria to match column B will not be an exact match.
I want it to find the number or match the next number higher. For example:
if the number to match is 35 then I want to return number 3, since it's the
next higher number than 35. Does that make sense?

I'm able to get the formula to work with an exact match, but not without.

Thanks.
Phyllis

Here's my formula:

{=IF(I1="S",INDEX(p15t!D7:D1491,MATCH('Employee 007'!D6&'Employee
007'!I2,p15t!B7:B1491&p15t!C7:C1491,1),IF('Employe e
007'!I1="M",INDEX(p15t!J7:J1502,MATCH('Employee 007'!D6&'Employee
007'!I2,p15t!H7:H1502&p15t!I7:I1502,1),0))))}

"Phyllis B." wrote:

Here's the scenario:

A B C
1 10 0
2 20 1
3 30 2
4 40 3

I want to index column C then match column A & B with criteria on another
worksheet. However the criteria to match column B will not be an exact match.
I want it to find the number or match the next number higher. For example:
if the number to match is 35 then I want to return number 3, since it's the
next higher number than 35. Does that make sense?

I'm able to get the formula to work with an exact match, but not without.

Thanks.
Phyllis

Hi
try something like the following array formula
=INDEX(C1:C100,MATCH(1,(A1:A100=4)*(B1:B10030),0) )

--
Regards
Frank Kabel
Frankfurt, Germany

"Phyllis B." <Phyllis schrieb im
Newsbeitrag ...
Here's the scenario:

A B C
1 10 0
2 20 1
3 30 2
4 40 3

I want to index column C then match column A & B with criteria on
another
worksheet. However the criteria to match column B will not be an
exact match.
I want it to find the number or match the next number higher. For
example:
if the number to match is 35 then I want to return number 3, since
it's the
next higher number than 35. Does that make sense?

I'm able to get the formula to work with an exact match, but not
without.

Thanks.
Phyllis