Index Match multiple criteria fuzzy lookup

[Demosassiner]

Hey guys, so after racking my brain for over a week trying to come out with a way to do this myself, I decided to ask for help. I don't even know if this is possible but here goes.

A colleague sends me the input, where he wants to match Microsoft Inc's Code "Micro" to Code "MICR" based on the exact number match of the number "0010"

Problem is, the "name" that he sends isn't always an exact match to the names in the main table, e.g. He sends Microsoft Inc which I need to match to Microsoft Ltd from the table.

I need a formula/macro/script to, match the "input" number with the "table" number, and then from all the table numbers' it matches, match the "input" name with the "table" name (which will have to be an approximate/fuzzy match), and then based on theses matches, return the appropriate CODE.

So, look at the main table and note all the number matches for 0010, then look at the names of these matches and do a best approximate match (Microsoft Inc to Microsoft Ltd), and then return the corresponding "from" and "to" codes (Micro to MICR).

Main Table

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Microsoft[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]MSFT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]0005[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]Microsoft Ltd[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]MICR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]Apple Inc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]AAPL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]Apple (Foreign)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]APLE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]Apple Co.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]APCO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0222[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]Facebook[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FCBK[/TD]
[TD]0050[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]Facebook Corp[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]FBBC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]0010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<tbody>[TR="class: outer_border"]
[TD="width: 65"]Facebook LLC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FACE[/TD]
[TD]0111[/TD]
[/TR]
</tbody>[/TABLE]


Table 2 (INPUT)

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Micro[/TD]
[TD]Microsoft Inc[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD]Apple123[/TD]
[TD]Apple[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD][TABLE="width: 195"]
<tbody>[TR="class: outer_border"]
[TD]Fbook1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Facebook Inc[/TD]
[TD]0010[/TD]
[/TR]
</tbody>[/TABLE]


Table 3 (OUTPUT)

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]From Code[/TD]
[TD]To Code[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Micro[/TD]
[TD]MICR[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD]Apple123[/TD]
[TD]AAPL[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD]Fbook1[/TD]
[TD]FBBC[/TD]
[TD]0010[/TD]
[/TR]
</tbody>[/TABLE]

 

[Demosassiner]

Re: Index Match multiple criteria fuzzy lookup help

Anyone?

 

[DHayes]

Re: Index Match multiple criteria fuzzy lookup help

Good Day,
I hope I understood your requirements. See Below the formulas I used for the outputs you need.
Use SHIFT+CTL+ENTER for the formulas. Not just enter.

---

---

Main Table
[TABLE="class: grid, width: 804"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Microsoft[/TD]
[TD]MSFT[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD]Microsoft Ltd[/TD]
[TD]MICR[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD]Apple Inc[/TD]
[TD]AAPL[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD]Apple (Foreign)[/TD]
[TD]APLE[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD]Apple Co.[/TD]
[TD]APCO[/TD]
[TD]0222[/TD]
[/TR]
[TR]
[TD]Facebook[/TD]
[TD]FCBK[/TD]
[TD]0050[/TD]
[/TR]
[TR]
[TD]Facebook Corp[/TD]
[TD]FBBC[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD]Facebook LLC[/TD]
[TD]FACE[/TD]
[TD]0111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Table 2 (INPUT)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Micro[/TD]
[TD]=INDEX(Sheet1!$A$1:$C$9,MATCH("Micro*"&Sheet1!$C12,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),1)[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD]Apple123[/TD]
[TD]=INDEX(Sheet1!$A$1:$C$9,MATCH("Apple*"&Sheet1!$C13,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),1)[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD]Fbook1[/TD]
[TD]=INDEX(Sheet1!$A$1:$C$9,MATCH("Face*"&Sheet1!$C14,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),1)[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Table 3 (OUTPUT)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]From Code[/TD]
[TD]To Code[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Micro[/TD]
[TD]=INDEX(Sheet1!$A$1:$C$9,MATCH("Micro*"&Sheet1!$C12,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),2)[/TD]
[TD]0010[/TD]
[/TR]
[TR]
[TD]Apple123[/TD]
[TD]=INDEX(Sheet1!$A$1:$C$9,MATCH("Apple*"&Sheet1!$C13,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),2)[/TD]
[TD]0005[/TD]
[/TR]
[TR]
[TD]Fbook1[/TD]
[TD]=INDEX(Sheet1!$A$1:$C$9,MATCH("Face*"&Sheet1!$C14,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),2)[/TD]
[TD]0010[/TD]
[/TR]
</tbody>[/TABLE]

Results
Table 2 (INPUT)
[TABLE="class: grid, width: 283"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Micro[/TD]
[TD]Microsoft Ltd[/TD]
[TD="align: right"]0010[/TD]
[/TR]
[TR]
[TD]Apple123[/TD]
[TD]Apple Inc[/TD]
[TD="align: right"]0005[/TD]
[/TR]
[TR]
[TD]Fbook1[/TD]
[TD]Facebook Corp[/TD]
[TD="align: right"]0010[/TD]
[/TR]
</tbody>[/TABLE]
Table 3 (OUTPUT)
[TABLE="class: grid, width: 283"]
<tbody>[TR]
[TD]From Code[/TD]
[TD]To Code[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Micro[/TD]
[TD]MICR[/TD]
[TD="align: right"]0010[/TD]
[/TR]
[TR]
[TD]Apple123[/TD]
[TD]AAPL[/TD]
[TD="align: right"]0005[/TD]
[/TR]
[TR]
[TD]Fbook1[/TD]
[TD]FBBC[/TD]
[TD="align: right"]0010[/TD]
[/TR]
</tbody>[/TABLE]

 

[Demosassiner]

Re: Index Match multiple criteria fuzzy lookup help

Don't mean to bump an older thread, but I'm desperate for a solution to this, particularly with regards to the "fuzzy" aspect of having several different, closely spelling inputs of the same name and needing to match it to one standardized one.

Anyone have experience with fuzzy lookup or any similar tool? I was thinking of concatenating the name with the number column e.g FacebookInc0010, and that this would make fuzzy lookup match to FacebookCorp0010 because the addition of the number would make it a closer match. This however, doesn't seem to be how the tool's logic works, and does more harm than good.

 

[Aladin Akyurek]

Re: Index Match multiple criteria fuzzy lookup help

main

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td] Name[/td][td] Code[/td][td] Number[/td][/tr]
[tr][td]

2​

[/td][td] Microsoft[/td][td] MSFT[/td][td] 0005[/td][/tr]
[tr][td]

3​

[/td][td] Microsoft Ltd[/td][td] MICR[/td][td] 0010[/td][/tr]
[tr][td]

4​

[/td][td] Apple Inc[/td][td] AAPL[/td][td] 0005[/td][/tr]
[tr][td]

5​

[/td][td] Apple (Foreign)[/td][td] APLE[/td][td] 0010[/td][/tr]
[tr][td]

6​

[/td][td] Apple Co.[/td][td] APCO[/td][td] 0222[/td][/tr]
[tr][td]

7​

[/td][td] Facebook[/td][td] FCBK[/td][td] 0050[/td][/tr]
[tr][td]

8​

[/td][td] Facebook Corp[/td][td] FBBC[/td][td] 0010[/td][/tr]
[tr][td]

9​

[/td][td] Facebook LLC[/td][td] FACE[/td][td] 0111[/td][/tr]
[/table]

io (input and output, where output = main code)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]Code[/td][td]Name[/td][td]Number[/td][td="bgcolor:#FFD966"]Main Code[/td][/tr]
[tr][td]

2​

[/td][td]Micro[/td][td]Microsoft Inc[/td][td]0010[/td][td="bgcolor:#FFD966"]MICR[/td][/tr]
[tr][td]

3​

[/td][td]Apple123[/td][td]Apple[/td][td]0005[/td][td="bgcolor:#FFD966"]AAPL[/td][/tr]
[tr][td]

4​

[/td][td]Fbook1[/td][td]Facebook Inc[/td][td]0010[/td][td="bgcolor:#FFD966"]FBBC[/td][/tr]
[/table]

In D2 of io control+shift+enter, not just enter, and copy down:

=IFERROR(VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH($A2,IF(main!$C$2:$C$9=$C2,main!$A$2:$A$9)),IF(main!$C$2:$C$9=$C2,main!$A$2:$A$9)),main!$A$2:$B$9,2,0),VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(LEFT($B2&" ",FIND(" ",$B2&" ")-1),IF(main!$C$2:$C$9=$C2,main!$A$2:$A$9)),IF(main!$C$2:$C$9=$C2,main!$A$2:$A$9)),main!$A$2:$B$9,2,0))

 

[Aladin Akyurek]

Re: Index Match multiple criteria fuzzy lookup help

When you adjust (edit) the ranges the formula of post #5 refers to, you must re-confirm the formula again with control+shift+enter.

P.S. Your PM box is full.

 

[Demosassiner]

Re: Index Match multiple criteria fuzzy lookup help

Hi Aladin- I was able to get this formula working- there was simply an extra space in the entry "ma in" instead of "main" probably due to the line break.

"=IFERROR(VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH($A2,IF(main!$C$2:$C$9=$C2,main!$A$2:$A$9)),IF(main!$C$2:$C$9=$C2,ma in!$A$2:$A$9)),main!$A$2:$B$9,2,0),VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(LEFT($B2&" ",FIND(" ",$B2&" ")-1),IF(main!$C$2:$C$9=$C2,main!$A$2:$A$9)),IF(main!$C$2:$C$9=$C2,main!$A$2:$A$9)),main!$A$2:$B$9,2,0))"

Following up on this, I was hoping you/anyone else could assist.

Let's say I introduce a new column to the main table, "Frequency", which is just how often the code is used. How could I go about revising this formula to say that, if the main table contains multiple entries with the similar/same "name", return the corresponding "code" based on which has the highest "frequency" - ignoring the "number" column (unlike my previous request).

E.g:

The names "Microsoft" and "Microsoft Ltd" are the same/similar.
Since Microsoft has rank 11 and Microsoft Ltd has rank 10, and 11>10, return corresponding code "MSFT" for Microsoft (as shown in column E).

Please see column E of the IO table below for the required output/results I'm trying to achieve.

Thanks for reading and I appreciate any help you guys could give!

main

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]Number[/TD]
[TD]Frequency[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Microsoft[/TD]
[TD]MSFT[/TD]
[TD]0005[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Microsoft Ltd[/TD]
[TD]MICR[/TD]
[TD]0010[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Apple Inc[/TD]
[TD]AAPL[/TD]
[TD]0005[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Apple (Foreign)[/TD]
[TD]APLE[/TD]
[TD]0010[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Apple Co.[/TD]
[TD]APCO[/TD]
[TD]0222[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Facebook[/TD]
[TD]FCBK[/TD]
[TD]0050[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Facebook Corp[/TD]
[TD]FBBC[/TD]
[TD]0010[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Facebook LLC[/TD]
[TD]FACE[/TD]
[TD]0111[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]


io (input and output, where output = main code)

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]D[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Number[/TD]
[TD]Frequency[/TD]
[TD="bgcolor: #FFD966"]Main Code[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Micro[/TD]
[TD]Microsoft [/TD]
[TD]0010[/TD]
[TD]11[/TD]
[TD="bgcolor: #FFD966"]MSFT[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Apple123[/TD]
[TD]Apple[/TD]
[TD]0005[/TD]
[TD]19[/TD]
[TD="bgcolor: #FFD966"]AAPL[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Fbook1[/TD]
[TD]Facebook Inc[/TD]
[TD]0010[/TD]
[TD]23[/TD]
[TD="bgcolor: #FFD966"]FBBC[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Re: Index Match multiple criteria fuzzy lookup help

In E2 of io, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(main!$B$2:$B$9,MATCH(MAX(IF(ISNUMBER(SEARCH(LEFT($B2&" ",FIND(" ",$B2&" ")-1),main!$A$2:$A$9)),main!$D$2:$D$9)),IF(ISNUMBER(SEARCH(LEFT($B2&" ",FIND(" ",$B2&" ")-1),main!$A$2:$A$9)),main!$D$2:$D$9),0)),"not found")