Trying to match/search 4 columns and taking data from a 5th to put in a 6th column

[rhino1301]

I'm trying to take two shorter(A/B) columns (last name/first name) and compare with two longer (D/E) columns (last name/first name). On the second set of columns there is a date attached (F). If the search finds a match can it put the corresponding next to the name in the first set (C). I am not an excel master and have been looking into this for way to long with no luck. Is it a combo of a vlookup and match? Help I'm kinda clueless.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]Doe[/TD]
[TD]John[/TD]
[TD]8/15/16[/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]Sam[/TD]
[TD][/TD]
[TD]Smith[/TD]
[TD]Chris[/TD]
[TD]8/15/16[/TD]
[/TR]
[TR]
[TD]Patterson[/TD]
[TD]Corey[/TD]
[TD][/TD]
[TD]Barnes[/TD]
[TD]Sally[/TD]
[TD]8/15/16[/TD]
[/TR]
[TR]
[TD]Ferguson[/TD]
[TD]Tim[/TD]
[TD][/TD]
[TD]Hall[/TD]
[TD]Kim[/TD]
[TD]12/8/15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Ryan[/TD]
[TD]12/8/15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]12/8/15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Morb[/TD]
[TD]Dustin[/TD]
[TD]12/8/15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Joseph[/TD]
[TD]Harry[/TD]
[TD]5/19/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Davis[/TD]
[TD]Sam[/TD]
[TD]5/19/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Davis[/TD]
[TD]Laura[/TD]
[TD]5/19/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rose[/TD]
[TD]Jim[/TD]
[TD]5/19/17[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

Try:

	A	B	C	D	E	F
Last Name	First Name	Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Smith[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12/8/2015[/TD]
[TD="bgcolor: #FAFAFA"]Doe[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8/15/2016[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Davis[/TD]
[TD="bgcolor: #FAFAFA"]Sam[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5/19/2017[/TD]
[TD="bgcolor: #FAFAFA"]Smith[/TD]
[TD="bgcolor: #FAFAFA"]Chris[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8/15/2016[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Patterson[/TD]
[TD="bgcolor: #FAFAFA"]Corey[/TD]
[TD="bgcolor: #FAFAFA"]No match[/TD]
[TD="bgcolor: #FAFAFA"]Barnes[/TD]
[TD="bgcolor: #FAFAFA"]Sally[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8/15/2016[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Ferguson[/TD]
[TD="bgcolor: #FAFAFA"]Tim[/TD]
[TD="bgcolor: #FAFAFA"]No match[/TD]
[TD="bgcolor: #FAFAFA"]Hall[/TD]
[TD="bgcolor: #FAFAFA"]Kim[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12/8/2015[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]No match[/TD]
[TD="bgcolor: #FAFAFA"]Johnson[/TD]
[TD="bgcolor: #FAFAFA"]Ryan[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12/8/2015[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Smith[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12/8/2015[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Morb[/TD]
[TD="bgcolor: #FAFAFA"]Dustin[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12/8/2015[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Joseph[/TD]
[TD="bgcolor: #FAFAFA"]Harry[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5/19/2017[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Davis[/TD]
[TD="bgcolor: #FAFAFA"]Sam[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5/19/2017[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Davis[/TD]
[TD="bgcolor: #FAFAFA"]Laura[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5/19/2017[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Rose[/TD]
[TD="bgcolor: #FAFAFA"]Jim[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5/19/2017[/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX($F$2:$F$12,MATCH(A2&"|"&B2,$D$2:$D$12&"|"&$E$2:$E$12,0)),"No match")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The formula in C2 is an array formula. Enter the formula in the formula bar, change the ranges to match your sheet, then press Control+Shift+Enter (not just enter). Then you can copy the formula down the column as far as needed.

Hope this helps.

 

[rhino1301]

I tried the formula and all I got for C2 was 42772. I guess another question I have is Do I have to have the locked area with $ signs or can I do an infinite column with A:A?

 

[63falcondude]

I tried the formula and all I got for C2 was 42772. I guess another question I have is Do I have to have the locked area with $ signs or can I do an infinite column with A:A?

Try changing the formatting of C2 from General to Date.

You do not want to use whole column references with array formulas as they will slow your spreadsheet down.

Here is a non-array option assuming that you do not have any names appearing more than once:

Excel 2010
	A	B	C	D	E	F	G
1	Last Name	First Name	Date		Last Name	First Name	Date
2	Smith	John	12/8/2015		Doe	John	8/15/2016
3	Davis	Sam	5/19/2017		Smith	Chris	8/15/2016
4	Patterson	Corey	No Match		Barnes	Sally	8/15/2016
5	Ferguson	Tim	No Match		Hall	Kim	12/8/2015
6					Johnson	Ryan	12/8/2015
7					Smith	John	12/8/2015
8					Morb	Dustin	12/8/2015
9					Joseph	Harry	5/19/2017
10					Davis	Sam	5/19/2017
11					Davis	Laura	5/19/2017
12					Rose	Jim	5/19/2017
Sheet1

Cell Formulas
Range	Formula
C2	=IFERROR(1/(1/SUMIFS(G:G,E:E,A2,F:F,B2)),"No Match")

 

[rhino1301]

That works perfectly thank you

 

[63falcondude]

That works perfectly thank you

Glad to help!