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

R

rhino1301

New Member
Joined
Apr 28, 2017
Messages
3
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]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Welcome to the forum.

Try:

ABCDEF
Last NameFirst NameDate

<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.
 
Upvote 0
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?
 
Upvote 0
rhino1301 said:
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?
Click to expand...

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
ABCDEFG
1Last NameFirst NameDateLast NameFirst NameDate
2SmithJohn12/8/2015DoeJohn8/15/2016
3DavisSam5/19/2017SmithChris8/15/2016
4PattersonCoreyNo MatchBarnesSally8/15/2016
5FergusonTimNo MatchHallKim12/8/2015
6JohnsonRyan12/8/2015
7SmithJohn12/8/2015
8MorbDustin12/8/2015
9JosephHarry5/19/2017
10DavisSam5/19/2017
11DavisLaura5/19/2017
12RoseJim5/19/2017
Sheet1
Cell Formulas
RangeFormula
C2=IFERROR(1/(1/SUMIFS(G:G,E:E,A2,F:F,B2)),"No Match")
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
First cell with a value of 0 after a certain row
2
Replies
10
Views
261
Fluff
Fluff
B
  • Question Question
VBA to show and change list of names depending on selection
Replies
7
Views
736
mumps
mumps
J
  • Question Question
Finding Numbers from a Column Using Target Numbers
2
Replies
11
Views
427
Cubist
Cubist
Y
  • Solved
Saving and Searching data to and through different Worksheet with User Form
Replies
2
Views
237
Yeft
Y
S
  • Solved
Transpose Data into multiple Columns
Replies
8
Views
303
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top