All solutions INDEX Expert Solution – Excel INDEX Problems

Excel - INDEX Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc