Fuzzy Logic in Excel to Check the string and give the pick the value from Defined range

[kuldeepjainesl]

Hello Champs,

I am struggling to get a value in B coloum if the any of the cell in the range D2:D8 is matching in coloum A:A in anyway like the red colour text. If the value is matching then pick the value from Coloum E.

 

[kuldeepjainesl]

Please refer attachment. Missed in upper one

 

[Nebu]

Hi:

Please find the attached.

Thanks

 

[Smallman]

Hi

A non array formula.

=IFERROR(LOOKUP(2^15,SEARCH($D$2:$D$10,A2),$E$2:$E$10),"")

Ensure your data in Col E is filled down (your result) and I added some items you omitted from your list. See attached for example. Another example FuzzyLogic

Take care

Smallman

 

[kuldeepjainesl]

Thanks Smallman and Nebu, Both solution are working....

 

[kuldeepjainesl]

May one please help me to understand the formula. why search is working as Array in non array formula...

=IFERROR(LOOKUP(2^15,SEARCH($D$2:$D$10,A2),$E$2:$E$10),"")

 

[Nebu]

Hi:

The maximum no of character a cell can hold is 32767, 2^15 will give you 32768 and this will be always be grater than the maximum possible return from a search function.

Thanks

 

[Nebu]

To add to the above
Search returns the position of the string (your look up values) being searched within the string, the max value as mentioned in the above post a cell can hold is 32,767, 2^15 (32768) is just one bigger than the maximum value . Look up evaluates a sorted (ascending) look up range and if it doesn't find a match, it will return the next item in the look up range which is less than the value looked for. Thus 2^15 match against the cells in the range being searched and returns a value(position) when the search is applied. This position in the range is then used to determine whether your look up values are present in the string or not.

Hope this helps...

 

[Smallman]

Hi kuldeepjainesl

The website I quoted in my post is my own, in anticipation of any such questions. It expains this formula as simply as I could put it.

Take care

Smallman