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

Function find the nearest match and provide the result

Hi Team,

I need a formula which match the nearest in lookup value & lookup array ignoring the Spaces, Special character like "-".

I upload sample file for your reference where is need Sheet 2 data in sheet 1 which are same but difference are Space & character. I can't remove the Space's or character in both of them .

I hope to find a solution soon
 

Attachments

  • Book1.xlsx
    8.9 KB · Views: 0
Check below...and attachment for your reference

INDEX(Sheet1!$A$3:$A$5,MATCH("*"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$4:$A$6,"-","")," ",REPT(" ",999)),999,32767))," ","*")&"*",Sheet1!$A$3:$A$5,0))

EDIT: CSE Enter
 

Attachments

  • Book1_Raghav.xlsx
    9.1 KB · Views: 0
Excellent Asheesh , Can we Get the Same in VBA also .as value in Sheet1 Range A4 is String which will have to Search in the range of Sheets2 Range A4 to A6 .
 
Hi Ragahav - I just changed the formula a lil bit...

Please use below..(non array)

INDEX(Sheet1!$A$3:$A$5,MATCH("*"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A4,"-","")," ",REPT(" ",999)),999,32767))," ","*")&"*",Sheet1!$A$3:$A$5,0))

I shall share the brief explanation of the above in a while..
 
Before you go through the explanation Click the below link to understand the REPT function
http://www.excelfunctions.net/Excel-Rept-Function.html
To understand the below formula INDEX(Sheet1!$A$3:$A$5,MATCH("*"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A4,"-","")," ",REPT(" ",999)),999,32767))," ","*")&"*",Sheet1!$A$3:$A$5,0))
The example in discussion is “1 - Technology Offshore” on sheet2
We shall first try to understand the below
TRIM(MID(SUBSTITUTE(SUBSTITUTE($A4,"-","")," ",REPT(" ",999)),999,32767)) once we start to evaluate this part of the formula we get
TRIM(MID(SUBSTITUTE("1 Technology Offshore"," ",REPT(" ",999)),999,32767)) where bold and underlined part i.e. SUBSTITUTE($A4,"-","") results into SUBSTITUTE("1 - Technology Offshore","-","") where $A4 = 1 - Technology Offshore.
Here, we have instructed this substitute function to substitute ”-“(dashes) with nothing(“”) once evaluated further it results into "1 Technology Offshore" and you will notice that there are no dashes (-) in this.
Now , our formula looks like TRIM(MID(SUBSTITUTE("1 Technology Offshore"," ",REPT(" ",999)),999,32767))
With this result, we shall now try understand the bold and underlined part of the above formula. i.e.
SUBSTITUTE("1 Technology Offshore"," ",REPT(" ",999) and this substitutes every space with 999 spaces, which means “1________________________________________________________________Technology________________________________________________________ Offshore”
Formula now looks like MID("1 _______________________________________________________________Technology _______________________________________________________ Offshore”999,32767)
Now, knowing the functionality of Mid, it picks up the string from 999th character to 32767 characters, where 32767 is the maximum length of a string that an excel cell can contain.
After applying the MID function wrapped in a TRIM function evaluates to "Technology Offshore"
So what do we have now - TRIM(MID(SUBSTITUTE(SUBSTITUTE($A4,"-","")," ",REPT(" ",999)),999,32767)) evaluates to "Technology Offshore"
Note: TRIM function replaces all the extra spaces from the text
Below is how our formula looks like now:
INDEX(Sheet1!$A$3:$A$5,MATCH("*"&SUBSTITUTE("Technology Offshore"," ","*")&"*",Sheet1!$A$3:$A$5,0))
Next, we shall again try to understand the bold and underlined part of the above formula, i.e.
SUBSTITUTE("Technology Offshore"," ","*") evaluates to "Technology*Offshore", notice the “*” (Asterisk) between the two words. Using this sign, helps us to search for a string which starts with Technology..and can have anything in between ..then ends with Offshore.
Moving to the next step..I place similar asterisk sign before and after these words using CONCATENATE function, that is the bold and underlined part of the below formula
INDEX(Sheet1!$A$3:$A$5,MATCH("*"&"Technology*Offshore"&"*",Sheet1!$A$3:$A$5,0)) this then evaluates to INDEX(Sheet1!$A$3:$A$5,MATCH("*Technology*Offshore*",Sheet1!$A$3:$A$5,0))
Now:
MATCH("*Technology*Offshore*",Sheet1!$A$3:$A$5,0) this part of the formula looks for a word which can have any in beginning but contains Technology and can have anything in the middle then is followed by Offshore...in the range Sheet1!$A$3:$A$5..so the formula MATCH("*Technology*Offshore*",Sheet1!$A$3:$A$5,0) evaluates to 1 i.e. the position of 1 - Technology – Offshore from sheet 1
Once wrapped into INDEX i.e. INDEX(Sheet1!$A$3:$A$5,1) results into 1 - Technology – Offshore
Hope this helps
 
Back
Top