Iterating through VLOOKUP matches

[durvur]

We have two lists of names zipcodes and states. The zipcodes and states are incomplete and first names are inconsistent Christopher vs Chris for example as well as multiple people having the same last name.
I want to do a look up on last name, iterate through multiple last names trying to match say the first 3-4 letters of first name, or match on state, or match on zipcode.
The iteration part im not familiar with. And I dont know where I would place a LEFT() function for first name matches or how I might wrap this formula in an IF statement to match against zipcode etc.
The foundation of this has to be a lookup on last name though. We have approached this from multiple other available vectors and what remains is possible matches based on inconsistency of first name, or state, or zip and multiple last name hits so, if anyone has a chunk of code that would give me some direction on how this logic would appear I'm pretty desperate. Even if its a chunk of VBA code I would have to edit.

 

[Mike Szczesny]

If I understand you correctly,can you use something like this? Hope this helps.

Excel 2012
	A	B	C	D	E	F	G	H	I
1	zip	code	first name	last name		zip	code	first name	last name
2	123	tx	chris	smith		123	tx	chris	smith
3	456		christoper	smith		456	0	christoper	smith
4		tex	kris	smith		0	tex	kris	smith
5	789		bill	Smith		789	0	bill	Smith
6	125	ut	Krist	smith		125	ut	Krist	smith
7			rob	Smythe		8990	ca	Chris	Smith
8	8990	ca	Chris	Smith		78	La	crist	smith
9	78	La	crist	smith
10	999		Chris	Schmitt
Sheet1

Cell Formulas
Range	Formula
F2	{=IFERROR(INDEX(A$2:A$10,SMALL(IF(ISNUMBER(SEARCH("Smith",$D$2:$D$10)),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($F$2:F2)))," ")}
G2	{=IFERROR(INDEX(B$2:B$10,SMALL(IF(ISNUMBER(SEARCH("Smith",$D$2:$D$10)),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($F$2:G2)))," ")}
H2	{=IFERROR(INDEX(C$2:C$10,SMALL(IF(ISNUMBER(SEARCH("Smith",$D$2:$D$10)),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($F$2:H2)))," ")}
I2	{=IFERROR(INDEX(D$2:D$10,SMALL(IF(ISNUMBER(IF(D$2:D$10<>"",SEARCH("Smith",$D$2:$D$10))),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($F$2:I2)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.