| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need greater than = .. less than = combined with index/match state
Excel 2003
I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match state
Try this...
=INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match s
Biff,
It works thank you! I see what you did. Can you explain to me what is happening? What does the ,0 do? Gary "T. Valko" wrote: Try this... =INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match s
MATCH is a standard Excel function, and the syntax is described in Excel
help. -- David Biddulph "Dingy101" wrote in message ... Biff, It works thank you! I see what you did. Can you explain to me what is happening? What does the ,0 do? Gary "T. Valko" wrote: Try this... =INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match s
When you have the match_type argument set to 0 that means you're looking for
an exact match. If an exact match isn't found then you get a #N/A error. Since you're using lookup_values that can be within a range, =0 <=5, then you want to set the match_type argument to 1 (or, you can omit the argument and it will default to 1). This tells the MATCH function that if an exact match isn't found then find the closest match that is less than the lookup_value. For example: MATCH(17,A1:A5) A1 = 0 A2 = 10 A3 = 20 A4 = 30 A5 = 40 Since there isn't an exact match of 17 it will match the closest number that is less than 17 and that number is 10. When using a match_type of 1 the lookup_array *MUST* be sorted in ascending order to get the correct result. -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Biff, It works thank you! I see what you did. Can you explain to me what is happening? What does the ,0 do? Gary "T. Valko" wrote: Try this... =INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match s
Thank You,
That was a helpful answer. It works great Gary "T. Valko" wrote: When you have the match_type argument set to 0 that means you're looking for an exact match. If an exact match isn't found then you get a #N/A error. Since you're using lookup_values that can be within a range, =0 <=5, then you want to set the match_type argument to 1 (or, you can omit the argument and it will default to 1). This tells the MATCH function that if an exact match isn't found then find the closest match that is less than the lookup_value. For example: MATCH(17,A1:A5) A1 = 0 A2 = 10 A3 = 20 A4 = 30 A5 = 40 Since there isn't an exact match of 17 it will match the closest number that is less than 17 and that number is 10. When using a match_type of 1 the lookup_array *MUST* be sorted in ascending order to get the correct result. -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Biff, It works thank you! I see what you did. Can you explain to me what is happening? What does the ,0 do? Gary "T. Valko" wrote: Try this... =INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match s
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Thank You, That was a helpful answer. It works great Gary "T. Valko" wrote: When you have the match_type argument set to 0 that means you're looking for an exact match. If an exact match isn't found then you get a #N/A error. Since you're using lookup_values that can be within a range, =0 <=5, then you want to set the match_type argument to 1 (or, you can omit the argument and it will default to 1). This tells the MATCH function that if an exact match isn't found then find the closest match that is less than the lookup_value. For example: MATCH(17,A1:A5) A1 = 0 A2 = 10 A3 = 20 A4 = 30 A5 = 40 Since there isn't an exact match of 17 it will match the closest number that is less than 17 and that number is 10. When using a match_type of 1 the lookup_array *MUST* be sorted in ascending order to get the correct result. -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Biff, It works thank you! I see what you did. Can you explain to me what is happening? What does the ,0 do? Gary "T. Valko" wrote: Try this... =INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match s
David,
Thank you very that very useless answer. That type of input is why forums dry up and wither. See elsewhere in thread for an answer that was actually very useful and appreciated. Gary "David Biddulph" wrote: MATCH is a standard Excel function, and the syntax is described in Excel help. -- David Biddulph "Dingy101" wrote in message ... Biff, It works thank you! I see what you did. Can you explain to me what is happening? What does the ,0 do? Gary "T. Valko" wrote: Try this... =INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary . . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need greater than = .. less than = combined with index/match s
You will learn a lot more about Excel by learning to get the most out of
Excel help. Of course, if you don't want to learn ... -- David Biddulph "Dingy101" wrote in message ... David, Thank you very that very useless answer. That type of input is why forums dry up and wither. See elsewhere in thread for an answer that was actually very useful and appreciated. Gary "David Biddulph" wrote: MATCH is a standard Excel function, and the syntax is described in Excel help. -- David Biddulph "Dingy101" wrote in message ... Biff, It works thank you! I see what you did. Can you explain to me what is happening? What does the ,0 do? Gary "T. Valko" wrote: Try this... =INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0) ) -- Biff Microsoft Excel MVP "Dingy101" wrote in message ... Excel 2003 I am using formula below succesfully , but I need to expand on it =INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3, 0)) What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,... I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ... 0 5 6 10 11 15 16 20 21 25 26 30 Then there is a range of static data filling in the rest of the array I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and K column and pick row that the value in A4 equal to or between. The above formula will work if I put into cell A4 the exact number that is in the range K3:K30, and ignore the value in L3:L30. I need the formula to work if the number 7 is entered in A4 the same as if 6 were entered into A4 In other words I need the match to work on = K3 and <= L3 Thanks, Gary . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup combined with Match formulas??? | Excel Discussion (Misc queries) | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| if less then zero put zero if greater then zero state number | Excel Worksheet Functions | |||
| vlookup and match combined? | Excel Worksheet Functions | |||
| ISNA(MATCH...) and IF functions - can they be combined? | Excel Worksheet Functions | |||
Linear Mode
