Index Match with duplicate values

[AnirbanM]

Hi Everyone,

I have an excel table from where I would have to fetch value from master data. The report table looks like this.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[/TR]
[TR]
[TD]Anir[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rex[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shiva[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rex[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tango[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would have to fetch values from the a master table to populate Date1, Date2 and Date3 columns.

The format of the master table looks like this
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[/TR]
[TR]
[TD]Anir[/TD]
[TD]23[/TD]
[TD]54[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]Rex[/TD]
[TD]88[/TD]
[TD]45[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Potter[/TD]
[TD]48[/TD]
[TD]96[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Shiva[/TD]
[TD]55[/TD]
[TD]66[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]Rex[/TD]
[TD]58[/TD]
[TD]98[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am using index match to populate the report table based on the Names, using index match, but since there are duplicate names with different values, only the values corresponding the first entry is being populated everywhere.

Can anyone please suggest any way to perform this operation with any relevant function along with Index Match?

Thanks in advance.

 

[Special-K99]

Not a good example since your master table contains no duplicate names and you haven't provided any expected output.

So if rex Date1 (88) was actually Anir Date1 (88) what would you expect to see in Anir Date 1 on your Report table ? 23 or 23+88 ?
Or something else?

Please provide expected output using duplicate names as you described.

 

[AhoyNC]

Maybe something like this if you have Excel ver. 2010 or later.

Excel Workbook
	A	B	C	D
1	Name	Date1	Date2	Date3
2	Anir	23	54	65
3	Rex	88	45	45
4	Potter	48	96	25
5	Shiva	55	66	78
6	Rex	58	98	45
7	Tango
8
9	Master Table
10	Name	Date1	Date2	Date3
11	Anir	23	54	65
12	Rex	88	45	45
13	Potter	48	96	25
14	Shiva	55	66	78
15	Rex	58	98	45
Sheet

 

[AnirbanM]

Hi,

Thank you for providing the feedback.

The output has to be in the same order as in the master data. it is because the master data gets refreshed each time.. but the output report should have hardcoded names (requirement asked) and only the values of the output table corresponding to the same names in the master data should be fetched.

Thanks in advance

 

[AhoyNC]

How about showing an example with the expected results based on your description above.