# Powerpivot Lookup

#### amotto11

##### New Member
Hi All,

I am basically trying to do a simple vlookup in PowerPivot. I am aware that powerpivot relationships act like a vlookup, but i am not trying to put my data into a pivot table, i am trying to keep everything in the PowerPivot table. here is an example of what i am trying to accomplish:

Table 1

Column 1 Column 2
A 1
B 2
C 3
D 4
E 5
F 6

Table 2

Column 1
A
B
C
G
H
Z

I would like to create an if(isna(vlookup)),0,1) statement in column 3 of Table 1. in excel the statement would be =if(isna(vlookup(Table1A in column1,1,False)),1,0). this would put a 1 in column 3 if A was found in column 1 of table 2, if it was not found it would put a zero. This way i could filter by the 1's in column three and get only that data(which i need). I cannot do this because like i said i have over 9 million rows which excel cannot handle. can anyone help me on this problem. Again i cannot create a pivot table like this and compare the information because it too would be over 2 million rows. that is why i need to be able to just filter the powerpivot table and grab the information that i need. I believe the formula i need is to create a relationship between the two column 1's and put in column three of table one, =if(related(column 1 in table 2)=cellA1 in table 1,1,0) although this formula is having some problems.

Any help would be greatly appretiated,

Thanks

Last edited:

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### powerpivotpro

##### Board Regular
Just to make sure, if you do have a relationship created, have you tried =RELATED()? That is the VLOOKUP equivalent in PowerPivot (once a relationship is in place).

#### amotto11

##### New Member
Just to make sure, if you do have a relationship created, have you tried =RELATED()? That is the VLOOKUP equivalent in PowerPivot (once a relationship is in place).

I have tried that, as i said in my original post i tried an if(related()) statement but i have no way of knowing that it was correct or not. it did put some ones in which is good but i am not sure if it put them all in. do you think that my if(related()) statement in the last line of my original post is working?

#### ruve1k

##### Board Regular
1. I think your are using the wrong tool to accomplish what you want. I believe you should be using SQL.
2. What is the relationship between Table1 and Table2? Which table has distinct values in column1?

#### amotto11

##### New Member

ruve1k,

I am sorry but i am not sure where to even begin on sql, i am not familiar with it at all.

I am using Table 2 as my lookup table in other words it is the table with only distinct values. Table one has repeating values in column 1.

#### ruve1k

##### Board Regular
1. Try to emulate the following SQL to obtain your data from the source.
Code:
``````SELECT Column1, Column2
FROM Table1
WHERE Column1 IN (SELECT Column1
FROM Table2
GROUP BY Column1)``````
2. For Table1[Column3] you could use something like:
Code:
``=ISTEXT(RELATED(Table2[Column1]))*1``

#### powerpivotpro

##### Board Regular

Hey amotto, are the columns of A, B, C, D, etc. in your two tables unique? Meaning, can a single value like A appear more than once?

#### ruve1k

##### Board Regular
Rob, I think he mentioned that.
I am using Table 2 as my lookup table in other words it is the table with only distinct values. Table one has repeating values in column 1.

#### powerpivotpro

##### Board Regular
Well crap I can't read today apparently

I do not understand at all why the =RELATED() function isn't working then. This should work fine.

=IF(ISBLANK(RELATED(Table2[Column1)),0,1)

Does that work?

#### amotto11

##### New Member
Hey amotto, are the columns of A, B, C, D, etc. in your two tables unique? Meaning, can a single value like A appear more than once?

In table 1 column 1 has repeating values (A is listed more than once). Table 2 only contains column 1, in which there are no repeating values, hence it is acting like my lookup table. I am wanting to check and see if the values from table 1 column 1 are in table 2 column 1.

Replies
0
Views
95
Replies
3
Views
88
Replies
0
Views
182
Replies
3
Views
170
Replies
2
Views
121