Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index match problems | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions | |||
index match and sum | Excel Worksheet Functions | |||
index to a range of cells | Excel Worksheet Functions |