Vlookup

[leimst]

Hello,

I am brand new to PowerPivot and from what I've read, I understand that PowerPivot is great and much more efficient at performing the equivalent of an Excel VLOOKUP although there is no such function in PowerPivot?

I have 2 spreadsheets. I would like to bounce the smaller spreadsheet up against the larger and return the data from certain columns.

Can anyone tell me how to accomplish that?

Any help would be greatly appreciated!

Leimst

 

[masplin]

I'm guessing you have a field in the big table that has unique values and in the smaller table you have a subset of the same field and want to pull data from the big table.

1.Put the 2 tables in the same excel workbook as different sheets and format as tables with sensible names.
2. On each table click on the powerpivot menu and "create linked table". This will copy the data from both tables into powerpivot into 2 tabs
3. Now create a relationship beteen the fields that have the common data. In powerpivot window click design/create relationship and put the sheet and column name that you want related. It wil lswitch thme automatically if you put them the wrong way round, but the "related lookup table" must contain unqiue values
4. In the small table you can now create new columns using the RELATED function to essentially lookup the value of your key field in the larger table and return the lookup.

Lets assume you have a small Table1 and a field UserID. You have the larger Table2 with columns UserID, Town, email

You create a relationship between Table1 UserID and Table2 UserID (this is the related lookup table)

In the Table1 tab of powerpivot enter a new column with the formula =RELATED(Table2[Town]). this will take the USerID of thel ine in the smal ltable, look at the big table and return the email address of that user.

Hope that is what you wanted. See link below

Mike

http://technet.microsoft.com/en-us/library/ee634202.aspx

 

[leimst]

Been so busy at work I hadn't even had a chance to try this but I did today and it worked perfectly!! Thank you so much!! Initially, I couldn't get it to work because as it turned out I had some duplicates in my "related lookup table".

Thanks again for the help!

Leimst

 

[linnzayar]

Dear

I also face the problem with Lookup function, it is that i can make result or take out data from Vlookup Function from 1 to 150 row exactly but when i search to take out data from more than 150 rows, it only show error.

Help me please

I am waiting your kind reply
linnzayar

 

[masplin]

Is this lookup like VLOOKUP in excel or the RELATED function in Powerpivot?

 

[linnzayar]

Vlookup in excel

 

[masplin]

ah you are in the powerpivot forum so you should really post your question in the excel forum where you will get a better answer

http://www.mrexcel.com/forum/forumdisplay.php?10-Excel-Questions

 

[gand3rson]

Is there anyway to match to a table in PP using the equivalent of a vlookup false? I want to do it using a calculated measure not a column in an existing table.

 

[masplin]

FALSE in VLOOKUP gives an exact match. As far as i'm aware the RELATED function in PP is also an exact match. It's doing the apporximate match that is hard in PP.

 

[gand3rson]

FALSE in VLOOKUP gives an exact match. As far as i'm aware the RELATED function in PP is also an exact match. It's doing the apporximate match that is hard in PP.

I meant true