#VALUE formula issue

Check your data - perhaps the data in M40:M261 is not of a form that you can convert it to a value.

Hope this helps.

Pete

Hi Pete

Thanks for your response!!

Wow..you are right !!

The formula was working perfect with values in M40:M261 -> 10001 (example)

The formula was not working with values in M40:M261 -> CC_70289 (example)

so I tried without VALUE function in both cases but it is giving !VALUE# error..

What should I try here ?

Thanks!!

If your data is all like that, i.e. with an underscore separating text and numbers, then you could do this:

=MAX(IF(VALUE(RIGHT(M261,LEN(M261)-SEARCH("_",M261)))=VALUE(RIGHT(M$40:M260,LEN(M$40:M260)-SEARCH("_",M$40:M260))),R$40:R260))+1

although you might not need it for the first term involving M261 (depending on what you put in there).

Note that this is an array formula, so you must commit it using the key combination Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

I tried this formula and it gives same issue #VALUE!

But when I selected single cell M$40:M40 instead of range M$40:M260 it worked perfect.. how to avoid this ?

Also this formula does not cover the cell with value -> CC_SA_680550

Could you please check is there any other function in excel available to achieve this.. ?

Appreciate your time and effort!!

I think Pete is not available

Can some one please have a look on my concern..

Thank you!!

I do eat now and then !!

I think you need to post an example workbook then we can see what variations you have in that range. The FAQ describes how to add a file to one of your posts.

Pete

Hi Pete

Thanks for coming back

I have attached excel for your reference..

Put this array* formula in K23:

Formula:

Please Login or Register  to view this content.

then copy down.

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter, as previously advised.

Hope this helps.

Pete

Hi Pete

You are great !! Thanks for your time and effort !!

I tried that key combination might be I missed something else..

Anyways I really appreciate and thanks to excelforum too

Cheers!!!

Well, you should get 4 in that cell and blanks in the other cells, as the attachment shows.

Hope this helps.

Pete

Sorry it was my bad english

Actually the issue is solved as per your previous post..

You are awesome !!