**Question description:**

*This user has given permission to use the problem statement for this blog.*

index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return

Solved by E. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
07/04/2018 - 11:44

Hello! Good day! How are you?

User
07/04/2018 - 11:44

good, you>

Excelchat Expert
07/04/2018 - 11:45

I'm good thank you. Are you able to provide a sample data that we could work on please?

User
07/04/2018 - 11:45

yes

User
07/04/2018 - 11:45

one min

Excelchat Expert
07/04/2018 - 11:47

sure. :)

User
07/04/2018 - 11:47

hold on

User
07/04/2018 - 11:49

u know what the problem is

User
07/04/2018 - 11:49

it is sensitive company information

User
07/04/2018 - 11:49

I dont know what to do

Excelchat Expert
07/04/2018 - 11:50

I have a general idea - its INDEX MATCH (), very similar to vlookup

User
07/04/2018 - 11:50

yes

Excelchat Expert
07/04/2018 - 11:50

it's okay, you're information will be kept confidential here

Excelchat Expert
07/04/2018 - 11:50

you don't have to give all the data. just a sample would do.

User
07/04/2018 - 11:52

hi

Excelchat Expert
07/04/2018 - 11:52

hello

User
07/04/2018 - 11:52

I uploaded a sheet

Excelchat Expert
07/04/2018 - 11:52

so we have 3 columns, correct? Call date, Duration, and Phone Number

Excelchat Expert
07/04/2018 - 11:53

oh, you added another one. Agent ID

User
07/04/2018 - 11:53

yes

User
07/04/2018 - 11:53

1 sec

Excelchat Expert
07/04/2018 - 11:53

sure

User
07/04/2018 - 11:56

ok

User
07/04/2018 - 11:56

to save some time

Excelchat Expert
07/04/2018 - 11:56

okay, where are we again?

User
07/04/2018 - 11:56

I have a formula already that when you populate the phone number it returns the duration

Excelchat Expert
07/04/2018 - 11:57

got it

User
07/04/2018 - 11:57

I need to do vlookup or index and match, but there are duplicates

Excelchat Expert
07/04/2018 - 11:58

it's cool. are you okay with adding another column for unique indexing of duplicates? (you could just hide it if it gets in the way of your presentation)

User
07/04/2018 - 11:58

its ok

Excelchat Expert
07/04/2018 - 11:58

okay, your source data is Copy of Sheet 1, right?

User
07/04/2018 - 11:58

after the 6 minutes are over how much is it?

User
07/04/2018 - 11:58

yes

Excelchat Expert
07/04/2018 - 11:59

I have no idea, what's visible to our end is just the points we earn for each session

User
08/04/2018 - 12:01

hmm

User
08/04/2018 - 12:01

well

User
08/04/2018 - 12:01

....

Excelchat Expert
08/04/2018 - 12:02

for a while, please

Excelchat Expert
08/04/2018 - 12:03

Hi. I don't seem to see any duplicates from column D

User
08/04/2018 - 12:03

wait one sec

User
08/04/2018 - 12:04

now

Excelchat Expert
08/04/2018 - 12:04

yes?

User
08/04/2018 - 12:05

now there are duplicates in sheet 1

Excelchat Expert
08/04/2018 - 12:06

Oh, I what i meant was there are no duplicates in Copy of Sheet 1 where the data will be pulled from

Excelchat Expert
08/04/2018 - 12:06

it's based on PhoneNumber, correct?

User
08/04/2018 - 12:07

yes

User
08/04/2018 - 12:07

as you type the phone number it fills the duration

User
08/04/2018 - 12:07

and date

User
08/04/2018 - 12:08

but it has to only return the duration and date if the agent id's match

User
08/04/2018 - 12:09

that is the formula I currently have on my desktop, one spreadsheet has 1500 phone numbers and the other 160000 phone calls I need for it to return the duration, because only those 1500 , the issue is that among those 1500 , one person called more than one time

Excelchat Expert
08/04/2018 - 12:11

Got it. So what I am trying to say is Copy of Sheet 1 is representative of the 160,000 phone calls and Sheet1 is representative of the 1500 phone numbers

Excelchat Expert
08/04/2018 - 12:11

correct?

Excelchat Expert
08/04/2018 - 12:15

To explain what i was doing, I added a column to identify the duplicate data under PhoneNumber in both sheets to display under the PhoneNumber_Index column

User
08/04/2018 - 12:15

there

User
08/04/2018 - 12:16

you see I am on line D407 in source data

User
08/04/2018 - 12:16

as I type in the number it has to return the duration and date,

Excelchat Expert
08/04/2018 - 12:16

alright

User
08/04/2018 - 12:16

however from this point and beyond I recognised the duration is not right because they contain duplicates

User
08/04/2018 - 12:17

in all calls month because the caller called more than once

User
08/04/2018 - 12:17

got it?

Excelchat Expert
08/04/2018 - 12:17

Yes,

Excelchat Expert
08/04/2018 - 12:17

so you match it using PhoneNumber

User
08/04/2018 - 12:17

yes....

Excelchat Expert
08/04/2018 - 12:17

from All calls month

User
08/04/2018 - 12:17

yes

User
08/04/2018 - 12:18

but they have duplicates

User
08/04/2018 - 12:18

vlookup would work too

User
08/04/2018 - 12:18

but there are duplicates

Excelchat Expert
08/04/2018 - 12:18

uhm, I don't seem to see phone number column in "all calls month"

Excelchat Expert
08/04/2018 - 12:18

okay, so column D

User
08/04/2018 - 12:18

yepp

Excelchat Expert
08/04/2018 - 12:19

i'll add a column to the right of column D okay?

User
08/04/2018 - 12:20

ok

Excelchat Expert
08/04/2018 - 12:21

okay, i highlighted 8883507270 as our sample duplicates

User
08/04/2018 - 12:21

how do I do that in excel?

Excelchat Expert
08/04/2018 - 12:21

just follow these steps

User
08/04/2018 - 12:22

ok

Excelchat Expert
08/04/2018 - 12:22

1. Insert a column to the right of column D

Excelchat Expert
08/04/2018 - 12:23

2. type the formula = D2&"_"&countif(D$2:D2,D2) to cell E2

Excelchat Expert
08/04/2018 - 12:23

3. Copy formula from cell E2 down to the last applicable row

User
08/04/2018 - 12:23

ok

User
08/04/2018 - 12:23

ok

Excelchat Expert
08/04/2018 - 12:23

can you follow so far?

User
08/04/2018 - 12:23

yes

Excelchat Expert
08/04/2018 - 12:24

Then an extra, you could use conditional formatting to highlight duplicate items

User
08/04/2018 - 12:24

ok

Excelchat Expert
08/04/2018 - 12:25

do you need further guidance on conditional formatting?

User
08/04/2018 - 12:25

its in the home tab in excel right

User
08/04/2018 - 12:26

ribbon/tab

Excelchat Expert
08/04/2018 - 12:26

yup highlight all of column D

User
08/04/2018 - 12:27

ok so just highlight cell rules, and all duplicate values

User
08/04/2018 - 12:27

got it

User
08/04/2018 - 12:27

where are u from?

Excelchat Expert
08/04/2018 - 12:27

then click, Home>Conditional Formatting>New Rule>Format Only Unique or Duplicate Values

Excelchat Expert
08/04/2018 - 12:27

yup

Excelchat Expert
08/04/2018 - 12:28

I am from Asia :)

User
08/04/2018 - 12:28

niceeee

User
08/04/2018 - 12:28

:)

Excelchat Expert
08/04/2018 - 12:28

anyway, let's proceed okay?

User
08/04/2018 - 12:28

yes

Excelchat Expert
08/04/2018 - 12:28

say we go back to source data

User
08/04/2018 - 12:28

ok

User
08/04/2018 - 12:29

how are we gonna know if it is a duplicate or not?

Excelchat Expert
08/04/2018 - 12:29

i also made the same column to the right of Phone Number

User
08/04/2018 - 12:30

ok using the same formula right

Excelchat Expert
08/04/2018 - 12:30

yup

User
08/04/2018 - 12:30

ok

Excelchat Expert
08/04/2018 - 12:30

now, for example we want to include in our list the phone number 8883507270

User
08/04/2018 - 12:31

yea

User
08/04/2018 - 12:31

and it is a duplicate

Excelchat Expert
08/04/2018 - 12:31

is it okay if I delete all data and start clean? i'll just retain the column headers

User
08/04/2018 - 12:31

yes

User
08/04/2018 - 12:32

now what?

Excelchat Expert
08/04/2018 - 12:32

for a while, let me build the formula

User
08/04/2018 - 12:33

ok

User
08/04/2018 - 12:33

thank you

Excelchat Expert
08/04/2018 - 12:34

hi i'm back

User
08/04/2018 - 12:34

ok

Excelchat Expert
08/04/2018 - 12:34

now, we just type the phone number 8883507270 in column D

Excelchat Expert
08/04/2018 - 12:36

so we just keep on doing typing the same number down until it stops showing values

Excelchat Expert
08/04/2018 - 12:36

wow there are lot of duplicates for this number

Excelchat Expert
08/04/2018 - 12:37

this person really calls a lot

User
08/04/2018 - 12:37

yes

Excelchat Expert
08/04/2018 - 12:37

does this solve your problem?

User
08/04/2018 - 12:37

that is why it needs to return only if the agent id matches

User
08/04/2018 - 12:37

so therotically

User
08/04/2018 - 12:37

I already have the agent ID and the PHOne number in the source data

User
08/04/2018 - 12:38

all 1500 calls

User
08/04/2018 - 12:38

just not now...

Excelchat Expert
08/04/2018 - 12:38

could you confirm if this solves your problem?

User
08/04/2018 - 12:38

so repeating in source data I already have phone number and agent ID, it just needs to return the duration and the date, for the current agent IS

User
08/04/2018 - 12:38

ID*

User
08/04/2018 - 12:38

it doesn't;r

User
08/04/2018 - 12:39

it doesn't

Excelchat Expert
08/04/2018 - 12:39

i see, so your other than phone number, it also has to match the agent ID?

User
08/04/2018 - 12:39

yes!!!

User
08/04/2018 - 12:39

exactly !!!

Excelchat Expert
08/04/2018 - 12:40

okay

Excelchat Expert
08/04/2018 - 12:40

for a while, i have to do some edits

User
08/04/2018 - 12:40

and if that same agent called still highight it

User
08/04/2018 - 12:40

ok thank you!

Excelchat Expert
08/04/2018 - 12:40

okay, let's take agent 956053

Excelchat Expert
08/04/2018 - 12:41

as example

Excelchat Expert
08/04/2018 - 12:41

i'll get back to you okay?

User
08/04/2018 - 12:41

ok

User
08/04/2018 - 12:42

in minimal cases the same agent took 2 calls so it will have two durations

Excelchat Expert
08/04/2018 - 12:42

okay

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*