Vlookup two columns and get value from third Column

[sharealong]

Hello Guru's,

I have been breaking my head to get the logic of Vlookup from two columns and get a value from third column.

Hope to find a quick solution here.

Question:

I have Last Name, First Name, SSN on Sheet 1, in coliumns A-C respectively.
I have only Last Name, First Name on Sheet 2, in Column A & B.
I need to do a Vlookup and get SSN from Sheet 1 to Sheet 2.

Issues:
Sheet 1 Data runs to 4,540 rows
Sheet 2 Data is 523 rows.
There are around 30 First Names which are same and around 70 Last Names which are same. with this if I do a Vlookup based on only one column, either Last Name or First Name, the formula is returning the first SSN it found for all the remaining Last Names, and it's the same with First Names.

Requirement:
I am looking at using a formula to check the Last Name, First Name and return the SSN.

Sheet 1
Last Name|First Name|SSN |
Joe |Todd |xxx-xx-xxxx|
Mike |Todd |xxx-xx-xxxx|
Mike |Wellens |xxx-xx-xxxx|
Joe |Hansen |xxx-xx-xxxx|

Sheet 2
Last Name|First Name|SSN |
Joe |Todd | |
Mike |Todd | |
Mike |Wellens | |
Joe |Hansen | |

Hope the description of the example is clear.

Thanks is advance, experts.

 

[dcardno]

On sheet 1 create a 'helper' column with the formula (in row 1):
= A1 & B1 (insert a column between the first name and the SSN)

On sheet 2 use the formula
= VLOOKUP(A3 & B3,Sheet1!$C$1:$D$23,2,FALSE) - and adjust the lookup range (ie C$1:$D$23) accordingly.

 

[T. Valko]

Hello Guru's,

I have been breaking my head to get the logic of Vlookup from two columns and get a value from third column.

Hope to find a quick solution here.

Question:

I have Last Name, First Name, SSN on Sheet 1, in coliumns A-C respectively.
I have only Last Name, First Name on Sheet 2, in Column A & B.
I need to do a Vlookup and get SSN from Sheet 1 to Sheet 2.

Issues:
Sheet 1 Data runs to 4,540 rows
Sheet 2 Data is 523 rows.
There are around 30 First Names which are same and around 70 Last Names which are same. with this if I do a Vlookup based on only one column, either Last Name or First Name, the formula is returning the first SSN it found for all the remaining Last Names, and it's the same with First Names.

Requirement:
I am looking at using a formula to check the Last Name, First Name and return the SSN.

Sheet 1
Last Name|First Name|SSN |
Joe |Todd |xxx-xx-xxxx|
Mike |Todd |xxx-xx-xxxx|
Mike |Wellens |xxx-xx-xxxx|
Joe |Hansen |xxx-xx-xxxx|

Sheet 2
Last Name|First Name|SSN |
Joe |Todd | |
Mike |Todd | |
Mike |Wellens | |
Joe |Hansen | |

Hope the description of the example is clear.

Thanks is advance, experts.

Try this...

Book1
	A	B	C
1	Last Name	First Name	SSN
2	Joe	Todd	100
3	Mike	Todd	200
4	Mike	Wellens	300
5	Joe	Hansen	400
Sheet1

Book1
	A	B	C
1	Last Name	First Name	SSN
2	Joe	Todd	_
3	Mike	Todd	_
4	Mike	Wellens	_
5	Joe	Hansen	_
Sheet2

Enter this array formula** in C2:

=INDEX(Sheet1!C$2:C$5,MATCH(1,IF(Sheet1!A$2:A$5=A2,IF(Sheet1!B$2:B$5=B2,1)),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down as needed.