vlookup vs Match+index

[Giancar]

Hi everyone,

I have 2 seperate table on 2 different sheet:
- a Cube Pivot Table
- a table that I paste from another source

Both table have a in Column A, an ID that is unique value for each row.
I need to merge these 2 table in a new table.

1st table (Cube pivot Table)
[TABLE="width: 500"]
<tbody>[TR]
[TD]1000[/TD]
[TD]jack[/TD]
[TD]x[/TD]
[TD]2016[/TD]
[TD]Q2[/TD]
[TD]MAY[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Jack[/TD]
[TD]b[/TD]
[TD]2016[/TD]
[TD]q2[/TD]
[TD]JUL[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]MARK[/TD]
[TD]X[/TD]
[TD]2018[/TD]
[TD]Q1[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]CHLOE[/TD]
[TD]V[/TD]
[TD]2017[/TD]
[TD]Q3[/TD]
[TD]SEP[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]MEL[/TD]
[TD]F[/TD]
[TD]2018[/TD]
[TD]Q1[/TD]
[TD]FEB[/TD]
[/TR]
</tbody>[/TABLE]


2nd table
[TABLE="width: 500"]
<tbody>[TR]
[TD]1000[/TD]
[TD]Jack[/TD]
[TD]5 stars[/TD]
[TD]Russia[/TD]
[TD]Moscow[/TD]
[/TR]
[TR]
[TD]1201[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1341[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]CHLOE[/TD]
[TD]4 Stars[/TD]
[TD]China[/TD]
[TD]Shangai[/TD]
[/TR]
</tbody>[/TABLE]


The result of this merge would like to have is the following
[TABLE="width: 500"]
<tbody>[TR]
[TD]1000[/TD]
[TD]jack[/TD]
[TD]2016[/TD]
[TD]q2[/TD]
[TD]may[/TD]
[TD]5 stars[/TD]
[TD]Russia[/TD]
[TD]moscow[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]chloe[/TD]
[TD]2017[/TD]
[TD]q3[/TD]
[TD]sep[/TD]
[TD]4 stars[/TD]
[TD]China[/TD]
[TD]Shangai[/TD]
[/TR]
</tbody>[/TABLE]


I do not know how, but I managed to make a Vlookup of the 2nd table with following formula: (DATA is the name of the pivot Table)
=VLOOKUP(A2,DATA,3,0)
=VLOOKUP(A2,DATA,4,0)
=VLOOKUP(A2,DATA,5,0)

When I try to do the same for the Cube pivot data, formula is returning #N/A. I tried also with following formula but is returning 0
=IFERROR(INDEX(DATA,MATCH($A2,sheet1!$A:$A,0),2),0)

I tried to see if the formatting is the same but it looks that everything is fine.
Any help please?

Thank you in advance

 

[Leandroarb]

Hi,

In the place of DATA, you write the name of range (Column) where are the data.

=IFERROR(INDEX(Column,MATCH($A2,sheet1!$A:$A,0),2),0)

 

[Giancar]

Hi,

Still 0!

 

[Leandroarb]

Do you writing the range that you need on the result?

 

[SpillerBD]

The Index is looking at a different Table/Range than the Match?

 

[Giancar]

The Index is looking at a different Table/Range than the Match?

Yes. Do you think it will help me getpivotdata?
@Leandro: I have tried range J2:J500000 and also J:J but still 0.

 

[Giancar]

Hi,

#N/A was the result because one was a number and the other column wasn't. I just added a 0 to the Vlookup formula and I have solved the issue.
thank you