Helper Column/ Duplicate Lookup values

[GN0001]

I have some data in sheet 1 that I have made a helper column for it:


John1 John ii

John2 John ff

Mary1 Mary gn

Guity1 Guity mm

Heidi1 Heidi ww

Guity2 Guity bb


Then I have some data in sheet 2 that I have made a helper column for it as well.

Guity1 Guity 1

John1 John 2

Heidi1 Heidi 3

John2 John 4

Mary1 Mary 5

Guity2 Guity 6


Now I want to use a VLookUp function: My look up value in sheet1 is Column A and I need to pick up data from sheet 2 from index column 3 and I enter the function in sheet 1:


I will get these values.

John1 John ii 2

John2 John ff 4

Mary1 Mary gn 5

Guity1 Guity mm 1

Heidi1 Heidi ww 3

Guity2 Guity bb 6


How do I know the John1 in sheet 1 is the same as John 1 in sheet 2? It could be possible that John1 which has ii in adjacent column is the John2 in sheet 2. Since we added a help column.


Can we use an array formula for this purpose?


Thank you for the help.

 

[Hui]

Guity

Have a look at : http://chandoo.org/wp/2010/11/02/multi-condition-lookup/

 

[GN0001]

Hui,

Thank you very much.

Guity