Powerpivot Lookup

amotto11

New Member
Joined
Feb 14, 2012
Messages
10
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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).
 
Upvote 0
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?
 
Upvote 0
  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?
 
Upvote 0
ruve1k,

Thank your for your response,

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.
 
Upvote 0
  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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top