Formula Help. Need a Vlookup type formula using multiple data to match

[Fanwood]

Hello to all!

Here is what I need help with and ill use examples:

on sheet1 I have Column A(names) and Column B(address)
on sheet2 I have Column A(names), B(address) and C(city)


if the names and adress match I need it to show C(city) on sheet1. If there is no match then it is left blank.


the names are in no particular order, could be spelled differently or incorrectly, hence the reason to find a match.

thank you

 

[PaddyD]

Hi - welcome to the board.

"... could be spelled differently or incorrectly..."

That is going to give you a whole heap of pain. If the names are important (couldn't you just drive it from the addresses?) then I think you'd be best getting familiar wit the name matching bit first. Play around with the functions here:

http://www.mrexcel.com/forum/hall-fame-winners/69649-alans-udfs-fuzzy-match-problem.html

Once you've made progress matching the names, you can move on to the next problem with the addresses.

Post back with examples etc if you can't get it sorted.

 

[Fanwood]

I only need the result if the name and address match on both sheets. Its fine if the name is incorrect. Can you help with a formula for that?

 

[Fanwood]

Hi - welcome to the board.

"... could be spelled differently or incorrectly..."

That is going to give you a whole heap of pain. If the names are important (couldn't you just drive it from the addresses?) then I think you'd be best getting familiar wit the name matching bit first. Play around with the functions here:

http://www.mrexcel.com/forum/hall-fame-winners/69649-alans-udfs-fuzzy-match-problem.html

Once you've made progress matching the names, you can move on to the next problem with the addresses.

Post back with examples etc if you can't get it sorted.

I am terrible at explaining this: On sheet1 state(c) should bring over data on sheet2 state(c) if A and B match on both sheets
example below:

[TABLE="width: 706"]
<tbody>[TR]
[TD]Sheet1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet2
[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]Full Name (A)
[/TD]
[TD]Address(B)
[/TD]
[TD]State(C)
[/TD]
[TD][/TD]
[TD]Full Name (A)
[/TD]
[TD="colspan: 2"]Address(B)
[/TD]
[TD]State(C)
[/TD]
[/TR]
[TR]
[TD]Joe shmo
[/TD]
[TD]123 alphabet lane
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Michele Jackson
[/TD]
[TD]555 Neverland
[/TD]
[TD="colspan: 2"]FL
[/TD]
[/TR]
[TR]
[TD]John Doe
[/TD]
[TD]1412 Tractor park
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bruce Wayne
[/TD]
[TD]Gotham City
[/TD]
[TD="colspan: 2"]MS
[/TD]
[/TR]
[TR]
[TD]Michele Jackson
[/TD]
[TD]555 Neverland
[/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe
[/TD]
[TD]24b baker street
[/TD]
[TD="colspan: 2"]FL
[/TD]
[/TR]
[TR]
[TD]Bruce Wayne
[/TD]
[TD]Gotham City
[/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe
[/TD]
[TD]1412 Tractor Park
[/TD]
[TD="colspan: 2"]AZ
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 706"]
<tbody>[TR]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet2[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]Full Name (A)[/TD]
[TD]Address(B)[/TD]
[TD]State(C)[/TD]
[TD][/TD]
[TD]Full Name (A)[/TD]
[TD="colspan: 2"]Address(B)[/TD]
[TD]State(C)[/TD]
[/TR]
[TR]
[TD]Joe shmo[/TD]
[TD]123 alphabet lane[/TD]
[TD][/TD]
[TD][/TD]
[TD]Michele Jackson[/TD]
[TD]555 Neverland[/TD]
[TD="colspan: 2"]FL[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]1412 Tractor park[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bruce Wayne[/TD]
[TD]Gotham City[/TD]
[TD="colspan: 2"]MS[/TD]
[/TR]
[TR]
[TD]Michele Jackson[/TD]
[TD]555 Neverland[/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe[/TD]
[TD]24b baker street[/TD]
[TD="colspan: 2"]FL[/TD]
[/TR]
[TR]
[TD]Bruce Wayne[/TD]
[TD]Gotham City[/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe[/TD]
[TD]1412 Tractor Park [/TD]
[TD="colspan: 2"]AZ[/TD]
[/TR]
</tbody>[/TABLE]

 

[Charlie45]

Try

=INDEX(Sheet2!$C$2:$C$500,MATCH(A1,IF(Sheet2!$B$2:$B$500=B1,Sheet2!$A$2:$A$500),0))

Don't just ENTER, do CTRL-SHIFT-ENTER

 

[Fanwood]

Try

=INDEX(Sheet2!$C$2:$C$500,MATCH(A1,IF(Sheet2!$B$2:$B$500=B1,Sheet2!$A$2:$A$500),0))

Don't just ENTER, do CTRL-SHIFT-ENTER

Hi Charlie,

I tried that but it does not give me correct results. Here is what it gave me and as you can see it is not correct. Any ideas?

[TABLE="width: 264"]
<tbody>[TR]
[TD]Full Name[/TD]
[TD]Address[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]Joe shmo[/TD]
[TD]123 alphabet lane[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]1412 Tractor park[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Michele Jackson[/TD]
[TD]555 Neverland[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Bruce Wayne[/TD]
[TD]Gotham City[/TD]
[TD]FL[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Charlie45]

Sorry! Typo!

Use this

=INDEX(Sheet2!$C$2:$C$500,MATCH(A2,IF(Sheet2!$B$2:$B$500=B2,Sheet2!$A$2:$A$500),0))

Don't just ENTER, do CTRL-SHIFT-ENTER

 

[Fanwood]

Works like a charm! Thank you, I cannot for the life of me workout why when I wrote my own it didnt work as it was almost identical! Tunnel vision I guess lol

I really appreciate your help.

 

[Charlie45]

Thanks for the feedback.

Glad I could help you find a solution.

 

[Fanwood]

When I try to use this on a much larger scale it does not seem to work. I am using a SS that goes up to 500,000, and pulls nothing...?