• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

lookup value in strings

Y

ysherriff

Member
i have a string of numbers in excel seperated by a character. I need to know the lookup formulat to extract a number sequence and then do a lookup.

See example below as well as sample file attached.

1003006008^1083866453^1225289895^1447342373^1497946453^1659562569^1861683823^1952592917


lookup value: 1659562569

return value:
INS00005696

the lookup and return value are in a table structure. The attached file is very small and explains all.

Thanks
 

Attachments

=INDEX(B2:B9,MAX(ROW(A2:A9)*NOT(LEN(SUBSTITUTE(A2:A9,A15,""))=LEN(A2:A9)))-1) Ctrl+Shift+Enter

or

=INDEX(B2:B9,MAX(ROW(A2:A9)*(LEN(SUBSTITUTE(A2:A9,A15,""))<>LEN(A2:A9)))-1) Ctrl+Shift+Enter

These both assume the value only appears once in all the cells
See attached file
 

Attachments

@prasaddn

9.99999999999999E+307 is the biggest number you can type in Excel and can be used for calculation purpose. Using this number in any LOOKUP formula will return the last number in the lookup vector.

Say you have a vector like {1;2;3;4;5} and you use formula like

=LOOKUP(9.99999999999999E+307,{1;2;3;4;5}) will return last number i.e. 5.

Now LOOKUP function ignore errors in lookup vector, so if you see the above formula i.e.

=LOOKUP(9.999E+307,SEARCH(A15,$A$2:$A$9),$B$2:$B$9)

here lookup vector will either be a number or an error, so LOOKUP will see the big number, it will not find it , but if there is any 1 successful search result that will result in a number will be less than this big number so lookup function will return the corresponding item from result vector.

The same can be used for string, you have to use a big string like "zzzzz", this will return the last string in the vector/array.

You can also use any big number suppose you expect the biggest number that can be there in your vector to be 1 than you can use 2 as big number.

Try below example
Put J13 = a, J14 = b, J15 = blank, J16 = blank, J17 = d, J 18=e.

Now from this vector you want last string than try putting below formula
=LOOKUP(2,1/(J13:J18<>""),J13:J18)

Try running this formula in evaluate mode and see the effect.

Write back if you want more explanation on this.

Regards,
 
You must log in or register to reply here.
Back
Top