lookup value in strings

[ysherriff]

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

 

[Hui]

=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

 

[ysherriff]

you are a ninja!! what would chandoo do without ya!! i appreciate it sir.

 

[Somendra Misra]

Hi,

Here's an alternative non-array formula:

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

Regards,

 

[prasaddn]

Hi SM,

Request you to kindly explain 9.999E+307 and how and when we can use this.

Regards,
Prasad DN.

 

[Somendra Misra]

@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,

 

[prasaddn]

Thank you for the detailed explanation.

Regards,
Prasad DN