# VLOOKUPVALUE - find first of many

#### Anthonsen

##### New Member
Hi

I have a table (table 1) in my powerpivot model, that contains customerID and where I calculate a column that add either A og B. Multiple rows have same customerID but each specefic customerID have always either A or B. .

Now I also have another table (table 2) that contains multiple rows with same customerID. In this table I would like to add the either A or B from table 1.

I have tried to use LOOKUPVALUE, but as it returns mutiple rows, it doesn't Work.
I can't make a list of distinct customerIDs, as the A or B is calculated in my model.

How can I get the A or B from my table 1 to my table 2?

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Anthonsen,

In table 1, does a given single customer have some A's and some B's? If not it seems to be a straightforward lookup. I am not sure I am seeing the whole picture what you mean by find first of many.

=VLOOKUP(customerID,'table 1'!,column#,false)

Luke

Hi Luke

Thanks.

I thought this was for PowerPivot questions - sorry it wasn't clear.

The two talbles could look like this:

Table 1
CustomerID AorB

1 A
1 A
1 A
2 A
3 B
3 B
4 A
4 A

Table 2

CustomerID
1
1
1
2
3
3
4

The two tabels are in a PowerPivot Model and I would like to add the AorB from table 1 to each customerID in table 2. AorB are calculated in the model, so it is not possible to add a table from the source groupedby customerID that could be used as lookup table.

Ideas are very welcome

Can you ever have something like the picture below where a customer can have both A and B values? If not (hopefully not), then I would use Power Query to create a new table from your table that has one row per customer. Then Power Pivot would allow you to draw a relationship to your new table and pickup the AorB value when you need it.

Hi

No - it will never happen that a customerID both have a row with A and a row with B.
Using PowerQuery sounds like a good idea, now that I have been reading about it. I have also been looking for powerquery in powerpivot, but can't find it?
Anyone who have a link to a description of that - or maybe easily can explain it?

Thanks.

Hi

Very cool

Both my tables are already in my powerpivot model - loaded from a SQL-server.
As I add the AorB column using a calculation in the powerpivot model, it is not possible to load it from the SQL-server using Groupby there.

I have PowerQuery in my Excel, but how do I use it on a table that are allready in Powerpivot?

You have to do Power Query -> Power Pivot rather than Power Pivot -> Power Query. Power Query is your ETL tool, like SSIS. In fact, Power Query will be supported in SSIS 2016, just like Power Pivot became supported in SSAS 2012.

Just use the Only Create Connection and Add this data to the Data Model options that I show in the video in order to send the Power Query results into Power Pivot.

However, if you already know SQL and your data is coming from SQL, I agree with you, just do a SELECT DISTINCT or GROUP BY on the query. I personally don't think Power Query offers a lot in this situation. Where it does offer a lot is when you're pulling from other data sources or you don't already know SQL.

Hi

I CANNOT do a select distict, as I need all lines to calculate the AorB column. The column that contains the string I could like to add to table 2.

Any ideas?

Thanks.

Can't you do this?

Code:
``````SELECT DISTINCT CustomerID,AorB
FROM Table1``````

Of course you could also do this:

Code:
``````SELECT CustomerID,AorB
FROM Table1
GROUP BY CustomerID,AorB``````

Replies
1
Views
415
Replies
0
Views
186
Replies
2
Views
387
Replies
2
Views
213
Replies
4
Views
861

1,196,048
Messages
6,013,093
Members
441,747
Latest member
darkman77

### 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.

### Which adblocker are you using?

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

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