Index Match multiple criteria fuzzy lookup

Demosassiner

New Member
Joined
Aug 13, 2017
Messages
7
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

NameCodeNumber
Microsoft
MSFT

<tbody>
</tbody>
0005

<tbody>
</tbody>
Microsoft Ltd

<tbody>
</tbody>
MICR

<tbody>
</tbody>
0010
Apple Inc

<tbody>
</tbody>
AAPL

<tbody>
</tbody>
0005
Apple (Foreign)

<tbody>
</tbody>
APLE

<tbody>
</tbody>
0010
Apple Co.

<tbody>
</tbody>
APCO

<tbody>
</tbody>
0222
Facebook

<tbody>
</tbody>
FCBK0050
Facebook Corp

<tbody>
</tbody>
FBBC

<tbody>
</tbody>
0010

<tbody>
</tbody>
Facebook LLC

<tbody>
</tbody>
FACE0111

<tbody>
</tbody>


Table 2 (INPUT)

CodeNameNumber
MicroMicrosoft Inc0010
Apple123Apple0005
Fbook1

<tbody>
</tbody>
Facebook Inc0010

<tbody>
</tbody>


Table 3 (OUTPUT)

From CodeTo CodeNumber
MicroMICR0010
Apple123AAPL0005
Fbook1FBBC0010

<tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
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
NameCodeNumber
MicrosoftMSFT0005
Microsoft LtdMICR0010
Apple IncAAPL0005
Apple (Foreign)APLE0010
Apple Co.APCO0222
FacebookFCBK0050
Facebook CorpFBBC0010
Facebook LLCFACE0111
Table 2 (INPUT)
CodeNameNumber
Micro=INDEX(Sheet1!$A$1:$C$9,MATCH("Micro*"&Sheet1!$C12,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),1)0010
Apple123=INDEX(Sheet1!$A$1:$C$9,MATCH("Apple*"&Sheet1!$C13,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),1)0005
Fbook1=INDEX(Sheet1!$A$1:$C$9,MATCH("Face*"&Sheet1!$C14,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),1)0010
Table 3 (OUTPUT)
From CodeTo CodeNumber
Micro=INDEX(Sheet1!$A$1:$C$9,MATCH("Micro*"&Sheet1!$C12,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),2)0010
Apple123=INDEX(Sheet1!$A$1:$C$9,MATCH("Apple*"&Sheet1!$C13,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),2)0005
Fbook1=INDEX(Sheet1!$A$1:$C$9,MATCH("Face*"&Sheet1!$C14,Sheet1!$A$1:$A$9&Sheet1!$C$1:$C$9,0),2)0010

<tbody>
</tbody>

Results
Table 2 (INPUT)
CodeNameNumber
MicroMicrosoft Ltd0010
Apple123Apple Inc0005
Fbook1Facebook Corp0010

<tbody>
</tbody>
Table 3 (OUTPUT)
From CodeTo CodeNumber
MicroMICR0010
Apple123AAPL0005
Fbook1FBBC0010

<tbody>
</tbody>
 
Last edited:

Demosassiner

New Member
Joined
Aug 13, 2017
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Re: Index Match multiple criteria fuzzy lookup help

main

Row\Col
A​
B​
C​
1​
Name Code Number
2​
Microsoft MSFT 0005
3​
Microsoft Ltd MICR 0010
4​
Apple Inc AAPL 0005
5​
Apple (Foreign) APLE 0010
6​
Apple Co. APCO 0222
7​
Facebook FCBK 0050
8​
Facebook Corp FBBC 0010
9​
Facebook LLC FACE 0111

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

Row\Col
A​
B​
C​
D​
1​
CodeNameNumberMain Code
2​
MicroMicrosoft Inc0010MICR
3​
Apple123Apple0005AAPL
4​
Fbook1Facebook Inc0010FBBC

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Aug 13, 2017
Messages
7
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

Row\Col
A​
B​
C​
D
1​
NameCodeNumberFrequency
2​
MicrosoftMSFT000511
3​
Microsoft LtdMICR001010
4​
Apple IncAAPL000519
5​
Apple (Foreign)APLE00105
6​
Apple Co.APCO022216
7​
FacebookFCBK00507
8​
Facebook CorpFBBC001023
9​
Facebook LLCFACE011115

<tbody>
</tbody>


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

Row\Col
A​
B​
C​
D
E​
1​
CodeNameNumberFrequencyMain Code
2​
MicroMicrosoft 001011MSFT
3​
Apple123Apple000519AAPL
4​
Fbook1Facebook Inc001023FBBC

<tbody>
</tbody>



 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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")
 

Forum statistics

Threads
1,144,394
Messages
5,724,087
Members
422,536
Latest member
Zeeshan53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top