Fuzzy Lookup Search for 2 or 3 columns

[kashifahmed]

I am stuck with finding fuzzy vertical lookup search and I also used Fuzzy lookup Add-in but its taking huge time and its hanging too as I have data around 45 lakhs Rows

Can you help me find fuzzy lookup for the below table. I want to fuzzy lookup with Column A&B with D&E and want to return the results in column G,H,I,J,K respectively.

Google Spreadsheet Link:

https://docs.google.com/spreadsheet/ccc?key=0Ah6hF01qdzWkdHFvVEp3OUlESlQzYVhja0hlaE9DSGc#gid=0

Looking forward for your prompt response and thanking you in advance.

Cheers
Kashif

 

[WinteE]

What does this 'Fuzzy lookup' do ? Please explain what you want to do.

 

[kashifahmed]

What does this 'Fuzzy lookup' do ? Please explain what you want to do.

Fuzzy Lookup looks up for partial match or similar match of the string, which Vlookup doesn't. Can you please have a look on my spreadsheet link.

Thanks

 

[azumi]

Assumes your data like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]No
[/TD]
[TD]Hoteel Name
[/TD]
[TD]Address
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Kadoma Hotel
[/TD]
[TD]Somewhere1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Ivory Lodge
[/TD]
[TD]Somewhere2
[/TD]
[/TR]
</tbody>[/TABLE]

you can use formula for randomize hotel names:

=VLOOKUP(RANDBETWEEN(1,2),$A$2:$B$11,2)

or

=INDEX($B$2:$B$11,RANDBETWEEN(1,2))

You can change the list (1,2) with your own list, maybe (1,100) of data

and for Hotel Address you can use regular VLOOKUP

Regards
Azumi

 

[kashifahmed]

@Azomi

Thanks for the reply, but I m sad to say it does not work. Actually I want to find a similar lookup including Hotel name and Address of the first table (Column A&B) with the second table (D&E Columns) which has similar matches (As Fuzzy Lookup Addin does, but this fuzzy lookup add-in takes a long time and it hangs up and sometimes never produces results).

Can you suggest any other alternative for this?

Thanks.

 

[azumi]

Start from Cell A1 to A15:
A B C
[TABLE="width: 500"]
<tbody>[TR]
[TD]No
[/TD]
[TD]Hotel Name 1
[/TD]
[TD]Hotel Address 1
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD="class: xl63, width: 212"]Kadoma Hotel And Conference[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 302"]
<tbody>[TR]
[TD="class: xl65, width: 302"]370 Hughes Center Drive Las Vegas, Nv 89109[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD="class: xl65, width: 212"]Southern Comfort Lodge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 302"]
<tbody>[TR]
[TD="class: xl65, width: 302"]3225 Paradise Road Las Vegas, Nv 89109[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Start from Cell E1 to E15:
E F
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Hotel Name 2
[/TD]
[TD]Hotel Address 2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hotel Kadoma and Conference
[/TD]
[TD]Hughes 370 Ctr Drv Las Vegas, Nv 89109
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lodge Southern Comfort
[/TD]
[TD]Paradise Road Las Vegas, Nv 89109
[/TD]
[/TR]
</tbody>[/TABLE]


try this formula (Array Formula):
=IFERROR(INDEX($E$2:$E$15;SMALL(IF(MAX(FREQUENCY(IF(ISERROR(SEARCH(MID($B2;ROW($A$1:INDEX($A$1:$A$100;LEN($B2)));1);TRANSPOSE($E$2:$E$15)));"";TRANSPOSE(ROW($E$2:$E$15)-MIN(ROW($E$2:$E$15))+1));ROW($1:$100)))=FREQUENCY(IF(ISERROR(SEARCH(MID($B2;ROW($A$1:INDEX($A$1:$A$100;LEN($B2)));1);TRANSPOSE($E$2:$E$15)));"";TRANSPOSE(ROW($E$2:$E$15)-MIN(ROW($E$2:$E$15))+1));ROW($1:$100));ROW($1:$100);"");COLUMN(A1)));"")

just change";" with "," because i'm living in indonesian systems

Azumi

 

[azumi]

Sorry the tables is a mess

hope it clear for u
Azumi

 

[kashifahmed]

Sorry the tables is a mess

hope it clear for u
Azumi

Hi Azumi:

I really appreciate your prompt responses and I really mean it. Can you please send me this through excel to my Email ID at kashifahmed.i@outlook.com so that I can use this formula for all of my data in excel as I am new to Excel and I am not understanding the above.

Looking forward for your mail and thanks in advance.

Kashif

 

[azumi]

Email sent

Azumi