|
How to avoid arrey formula?
Here is my data. DT NAME QTY 02-May AA 2 02-May AA 2 02-May BB 2 02-May CC 2 06-Aug AA 6 06-Aug BB 6 06-Aug CC 6 07-Aug AA 7 07-Aug BB 7 07-Aug CC 7 09-Aug AA 9 09-Aug BB 9 09-Aug CC 9 10-Aug AA 10 10-Aug BB 10 10-Aug CC 10 I want to lookup latest value of BB which is in this case 10 (I want just lookup value, sum or count is not required) My arrey formula is =INDEX(LTP,MAX(IF(Code=A55,ROW(Code)))) This data is dynamic, i.e. everyday 8-10 row of data will be added. Is there a way to avoid the arrey formula in this case? Regards, Madiya |
How to avoid arrey formula?
Something like this maybe...
=IF(COUNTIF(B:B,"BB")0,LOOKUP(2,1/(B1:B1000="BB"),A:A),"") where you would set the 1000 in B1000 to the maximum row number you would ever expect to have data in. -- Rick (MVP - Excel) "Madiya" wrote in message ... Here is my data. DT NAME QTY 02-May AA 2 02-May AA 2 02-May BB 2 02-May CC 2 06-Aug AA 6 06-Aug BB 6 06-Aug CC 6 07-Aug AA 7 07-Aug BB 7 07-Aug CC 7 09-Aug AA 9 09-Aug BB 9 09-Aug CC 9 10-Aug AA 10 10-Aug BB 10 10-Aug CC 10 I want to lookup latest value of BB which is in this case 10 (I want just lookup value, sum or count is not required) My arrey formula is =INDEX(LTP,MAX(IF(Code=A55,ROW(Code)))) This data is dynamic, i.e. everyday 8-10 row of data will be added. Is there a way to avoid the arrey formula in this case? Regards, Madiya |
How to avoid arrey formula?
Hi all!
Madiya , try this: =INDEX($C:$C,MATCH("BB",$B:$B,1)) -- John Ο χρήστης "Rick Rothstein" *γγραψε: Something like this maybe... =IF(COUNTIF(B:B,"BB")0,LOOKUP(2,1/(B1:B1000="BB"),A:A),"") where you would set the 1000 in B1000 to the maximum row number you would ever expect to have data in. -- Rick (MVP - Excel) "Madiya" wrote in message ... Here is my data. DT NAME QTY 02-May AA 2 02-May AA 2 02-May BB 2 02-May CC 2 06-Aug AA 6 06-Aug BB 6 06-Aug CC 6 07-Aug AA 7 07-Aug BB 7 07-Aug CC 7 09-Aug AA 9 09-Aug BB 9 09-Aug CC 9 10-Aug AA 10 10-Aug BB 10 10-Aug CC 10 I want to lookup latest value of BB which is in this case 10 (I want just lookup value, sum or count is not required) My arrey formula is =INDEX(LTP,MAX(IF(Code=A55,ROW(Code)))) This data is dynamic, i.e. everyday 8-10 row of data will be added. Is there a way to avoid the arrey formula in this case? Regards, Madiya |
How to avoid arrey formula?
On Sep 15, 5:28*pm, John_John
wrote: Hi all! Madiya , try this: =INDEX($C:$C,MATCH("BB",$B:$B,1)) -- John "Rick Rothstein" : Something like this maybe... =IF(COUNTIF(B:B,"BB")0,LOOKUP(2,1/(B1:B1000="BB"),A:A),"") where you would set the 1000 in B1000 to the maximum row number you would ever expect to have data in. -- Rick (MVP - Excel) "Madiya" wrote in message .... Here is my data. DT NAME QTY 02-May AA 2 02-May AA 2 02-May BB 2 02-May CC 2 06-Aug AA 6 06-Aug BB 6 06-Aug CC 6 07-Aug AA 7 07-Aug BB 7 07-Aug CC 7 09-Aug AA 9 09-Aug BB 9 09-Aug CC 9 10-Aug AA 10 10-Aug BB 10 10-Aug CC 10 I want to lookup latest value of BB which is in this case 10 (I want just lookup value, sum or count is not required) My arrey formula is =INDEX(LTP,MAX(IF(Code=A55,ROW(Code)))) This data is dynamic, i.e. everyday 8-10 row of data will be added. Is there a way to avoid the arrey formula in this case? Regards, Madiya- Hide quoted text - - Show quoted text - Rick, Thank you for your help and sorry for delay in my reply. Your formula result is null i.e. cell shows blank. I have tried some varients also but no result. Any idea, whats wrong with me? John, Thanks but formula shows only the value in last row. I want last value of BB which may not be in last row. Thanks again for your help and efforts. Regards, Madiya. |
How to avoid arrey formula?
I'm not sure what to tell you... I just re-tested my formula on your data
and it works fine here. I even made everything text just in case your dates and numbers were not real dates and numbers, but only text that looked like them... the formula still worked fine. Your data is in Columns A, B and C, right? What row does your data start on? -- Rick (MVP - Excel) "Madiya" wrote in message ... On Sep 15, 5:28 pm, John_John wrote: Hi all! Madiya , try this: =INDEX($C:$C,MATCH("BB",$B:$B,1)) -- John "Rick Rothstein" : Something like this maybe... =IF(COUNTIF(B:B,"BB")0,LOOKUP(2,1/(B1:B1000="BB"),A:A),"") where you would set the 1000 in B1000 to the maximum row number you would ever expect to have data in. -- Rick (MVP - Excel) "Madiya" wrote in message ... Here is my data. DT NAME QTY 02-May AA 2 02-May AA 2 02-May BB 2 02-May CC 2 06-Aug AA 6 06-Aug BB 6 06-Aug CC 6 07-Aug AA 7 07-Aug BB 7 07-Aug CC 7 09-Aug AA 9 09-Aug BB 9 09-Aug CC 9 10-Aug AA 10 10-Aug BB 10 10-Aug CC 10 I want to lookup latest value of BB which is in this case 10 (I want just lookup value, sum or count is not required) My arrey formula is =INDEX(LTP,MAX(IF(Code=A55,ROW(Code)))) This data is dynamic, i.e. everyday 8-10 row of data will be added. Is there a way to avoid the arrey formula in this case? Regards, Madiya- Hide quoted text - - Show quoted text - Rick, Thank you for your help and sorry for delay in my reply. Your formula result is null i.e. cell shows blank. I have tried some varients also but no result. Any idea, whats wrong with me? John, Thanks but formula shows only the value in last row. I want last value of BB which may not be in last row. Thanks again for your help and efforts. Regards, Madiya. |
How to avoid arrey formula?
On Sep 17, 12:30*pm, "Rick Rothstein"
wrote: I'm not sure what to tell you... I just re-tested my formula on your data and it works fine here. I even made everything text just in case your dates and numbers were not real dates and numbers, but only text that looked like them... the formula still worked fine. Your data is in Columns A, B and C, right? What row does your data start on? -- Rick (MVP - Excel) "Madiya" wrote in message ... On Sep 15, 5:28 pm, John_John wrote: Hi all! Madiya , try this: =INDEX($C:$C,MATCH("BB",$B:$B,1)) -- John "Rick Rothstein" : Something like this maybe... =IF(COUNTIF(B:B,"BB")0,LOOKUP(2,1/(B1:B1000="BB"),A:A),"") where you would set the 1000 in B1000 to the maximum row number you would ever expect to have data in. -- Rick (MVP - Excel) "Madiya" wrote in message .... Here is my data. DT NAME QTY 02-May AA 2 02-May AA 2 02-May BB 2 02-May CC 2 06-Aug AA 6 06-Aug BB 6 06-Aug CC 6 07-Aug AA 7 07-Aug BB 7 07-Aug CC 7 09-Aug AA 9 09-Aug BB 9 09-Aug CC 9 10-Aug AA 10 10-Aug BB 10 10-Aug CC 10 I want to lookup latest value of BB which is in this case 10 (I want just lookup value, sum or count is not required) My arrey formula is =INDEX(LTP,MAX(IF(Code=A55,ROW(Code)))) This data is dynamic, i.e. everyday 8-10 row of data will be added. Is there a way to avoid the arrey formula in this case? Regards, Madiya- Hide quoted text - - Show quoted text - Rick, Thank you for your help and sorry for delay in my reply. Your formula result is null i.e. cell shows blank. I have tried some varients also but no result. Any idea, whats wrong with me? John, Thanks but formula shows only the value in last row. I want last value of BB which may not be in last row. Thanks again for your help and efforts. Regards, Madiya.- Hide quoted text - - Show quoted text - Rick, I am really sorry. Your formulla works fine. It was my mistake, I was trying this formula on the sample data where it was only returning blank cell. The reason was a white space before the values like " BB", " AA" etc. When I recreate the sample data, your formula worked perfectly and exactly as I expacted. I appreciate your help and time spent with me. Regards, Madiya |
| All times are GMT +1. The time now is 03:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com