How to avoid arrey formula?

[Madiya]

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

 

[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.

 

[John_John]

Hi all!

Madiya , try this:

=INDEX($C:$C,MATCH("BB",$B:$B,1))

 

[Madiya]

Hi all!

Madiya , try this:

=INDEX($C:$C,MATCH("BB",$B:$B,1))

--
John

Ï ÷ñÞóôçò "Rick Rothstein" Ýããñáøå:




- 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.

 

[Rick Rothstein]

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)

Hi all!

Madiya , try this:

=INDEX($C:$C,MATCH("BB",$B:$B,1))

--
John

Ï ÷ñÞóôçò "Rick Rothstein" Ýããñáøå:




- 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.

 

[Madiya]

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)






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