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? |
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? |
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? |
All times are GMT +1. The time now is 02:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com