Vlookup with three criterias

[alfredo197135]

Hi guys,

Can you tell me how to use Vlookup with three criterias to find?

 

[Aladin Akyurek]

I want to use the above formula with 2 criterias, I modified the formula accordingly but it is not working. Please help me correct it...

=INDEX(Sheet2!G2:G110511,MATCH(1,IF(Sheet2!E2:E110511=A2,if(Sheet2!F2:F110511=B2))),0))

Control+shift+enter, not just enter:

Either...

=INDEX(Sheet2!G2:G110511,MATCH(1,IF(Sheet2!E2:E110511=A2,if(Sheet2!F2:F110511=B2,1)),0))

Or...

=INDEX(Sheet2!G2:G110511,MATCH(B2,IF(Sheet2!E2:E110511=A2,Sheet2!F2:F110511),0))

 

[excel_1317]

Control+shift+enter, not just enter:

Either...

=INDEX(Sheet2!G2:G110511,MATCH(1,IF(Sheet2!E2:E110511=A2,if(Sheet2!F2:F110511=B2,1)),0))

Or...

=INDEX(Sheet2!G2:G110511,MATCH(B2,IF(Sheet2!E2:E110511=A2,Sheet2!F2:F110511),0))

Thank you for this! This is currently working for EXACT matches, can it work for similar matches?

 

[Aladin Akyurek]

Thank you for this! This is currently working for EXACT matches, can it work for similar matches?

Do you mean approximate match? If so, you need to state whether the match ranges are in ascending order, that is, Sheet2!E2:E110511 and Sheet2!F2:F110511.

 

[excel_1317]

i have 2 lists. List 1 has company names and contact names against it.
List 2 has Company names with contact names and emails against it. So in both lists contacts are similar names but not exact. FOR eg: List 1 has Microsoft Corp. with Contact Name as Karen Walters.
List 2 has Microsoft Corp. with Contact name as Karen A Walters with email as kwalters@microsoft.com.

So i just need to pull that email on the basis of company names and contact names.

 

[Aladin Akyurek]

i have 2 lists. List 1 has company names and contact names against it.
List 2 has Company names with contact names and emails against it. So in both lists contacts are similar names but not exact. FOR eg: List 1 has Microsoft Corp. with Contact Name as Karen Walters.
List 2 has Microsoft Corp. with Contact name as Karen A Walters with email as kwalters@microsoft.com.

So i just need to pull that email on the basis of company names and contact names.

We have a case of partial match. You probably need something like:

http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

See if the following might suffice.

List2, Sheet2, A2:C4...

[TABLE="width: 266"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 4977" width=140><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5376" width=151><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]MS[/TD]
[TD="class: xl65, width: 140, bgcolor: transparent"]Karen A Walters[/TD]
[TD="class: xl66, width: 151, bgcolor: transparent"]walters@ms.com[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]IBM[/TD]
[TD="class: xl65, bgcolor: transparent"]Luke Donovan[/TD]
[TD="class: xl66, bgcolor: transparent"]donovan@ibm.com[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Apple[/TD]
[TD="class: xl65, bgcolor: transparent"]Vidar[/TD]
[TD="class: xl66, bgcolor: transparent"]vidar@apple.com[/TD]
[/TR]
</TBODY>[/TABLE]

List1, Sheet1, A2:C5...

[TABLE="width: 248"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4551" width=128><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 4920" width=138><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]MS[/TD]
[TD="class: xl65, width: 128, bgcolor: transparent"]Karen Walters[/TD]
[TD="class: xl65, width: 138, bgcolor: transparent"]walters@ms.com[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Shell[/TD]
[TD="class: xl65, bgcolor: transparent"]Greg[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Apple[/TD]
[TD="class: xl65, bgcolor: transparent"]Vidar[/TD]
[TD="class: xl65, bgcolor: transparent"]vidar@apple.com[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]IBM[/TD]
[TD="class: xl65, bgcolor: transparent"]Luke Donovan[/TD]
[TD="class: xl65, bgcolor: transparent"]donovan@ibm.com[/TD]
[/TR]
</TBODY>[/TABLE]

C2, control+shift+enter, not just enter, and copy down:

=INDEX(Sheet2!$C$2:$C$4,MATCH(SUBSTITUTE(B2," ","*"),
  IF(Sheet2!$A$2:$A$4=A2,Sheet2!$B$2:$B$4),0))

 

[T. Valko]

i have 2 lists. List 1 has company names and contact names against it.
List 2 has Company names with contact names and emails against it. So in both lists contacts are similar names but not exact. FOR eg: List 1 has Microsoft Corp. with Contact Name as Karen Walters.
List 2 has Microsoft Corp. with Contact name as Karen A Walters with email as kwalters@microsoft.com.

So i just need to pull that email on the basis of company names and contact names.

Can you post some sample data and tell us what results you expect?

 

[jamilm]

your formulas are astonishing Aladin!

Control+shift+enter, not just enter:

Either...

=INDEX(Sheet2!G2:G110511,MATCH(1,IF(Sheet2!E2:E110511=A2,if(Sheet2!F2:F110511=B2,1)),0))

Or...

=INDEX(Sheet2!G2:G110511,MATCH(B2,IF(Sheet2!E2:E110511=A2,Sheet2!F2:F110511),0))

 

[Aladin Akyurek]

your formulas are astonishing Aladin!

Great to hear that they are useful to you.

 

[jamilm]

Great to hear that they are useful to you.

Thank you Aladin.

I have learned a great amount of excel knowledge from you

appreciated

 

[excel_1317]

Control+shift+enter, not just enter:

Either...

=INDEX(Sheet2!G2:G110511,MATCH(1,IF(Sheet2!E2:E110511=A2,if(Sheet2!F2:F110511=B2,1)),0))

Or...

=INDEX(Sheet2!G2:G110511,MATCH(B2,IF(Sheet2!E2:E110511=A2,Sheet2!F2:F110511),0))

Syntax for MATCH function is match(lookup_value,array, type of match). Now in above formula how array is forming in match function?

MATCH(B2,IF(Sheet2!E2:E110511=A2,Sheet2!F2:F110511),0))... Please help me understand this part. Thanks!

I know how nested if works.