Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & Match Question
One last question:
How would this be modified to find the closest or next smaller value? =INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'! $BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0)) For example, if I am trying to match .625 and 5000 and the table has: ..625 4000 .55 ..625 4750 .65 ..625 5500 .25 ..625 6000 .35 How do I get it to return the .65? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & Match Question
Hi,
Maybe this =OFFSET(INDEX(C1:C4,MATCH(1,(A1:A4=D1)*(B1:B4=E1) ,0)),-1,0) With 0.625 in D1 and 5000 in E1 This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Iriemon" wrote: One last question: How would this be modified to find the closest or next smaller value? =INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'! $BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0)) For example, if I am trying to match .625 and 5000 and the table has: .625 4000 .55 .625 4750 .65 .625 5500 .25 .625 6000 .35 How do I get it to return the .65? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & Match Question
What should happen if the 2nd lookup value is less than the samllest value
or greater than the highest value? For example, lookup 0.625 and 3550, or, lookup 0.625 and 7000. -- Biff Microsoft Excel MVP "Iriemon" wrote in message ... One last question: How would this be modified to find the closest or next smaller value? =INDEX('Sheet2'!$BH$2:$BH$94,MATCH(O2&T2,'Sheet2'! $BF$2:$BF$94&'Sheet2'!$BG$2:$BG$94,0)) For example, if I am trying to match .625 and 5000 and the table has: .625 4000 .55 .625 4750 .65 .625 5500 .25 .625 6000 .35 How do I get it to return the .65? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index / Match Question | Excel Worksheet Functions | |||
Index match question | Excel Discussion (Misc queries) | |||
Index/match question. | Excel Discussion (Misc queries) | |||
Index/match question. | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions |