Forum Discussion

Vestments7's avatar
Vestments7
Regular Visitor
3 years ago

VLOOKUP OR INDEXMATCH ALTERNATIVE TO FIND VALUE BASED ON CELL VALUE

This is what I have:   ID Name 1   1   1 A 2   2 B 2   3   3   3   3 C   And this is what I need: ID Name 1 A 1   1   2 B 2 ...
Need Help
Show and Tell
serpiva64's avatar
serpiva64
Solution Sage
3 years ago

Hi,

you can obtain

by grouping 

then adding a nested index column

expanding

and finally adding a conditional column

You can see the steps in the attached file

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

 

 

Vestments7's avatar
Vestments7
Regular Visitor
3 years ago

Cool and thanks for your answer. But this is not I am looking for as this in DAX environment. But how to do that using PowerPivot (PowerQuery M) that is built in Excel? There is no Group by feature and also no Table.AddIndexColumn formula.

 
 
 
 
 
  • serpiva64's avatar
    serpiva64
    Solution Sage
    3 years ago

    Hi,

    Here is the project in excel.

    the commands between BI and excel a quite the same

     

    • Vestments7's avatar
      Vestments7
      Regular Visitor
      3 years ago

      Thank you for your message. You are using Query, but mine is loaded as Data Model and use PowerPivot. How to do that using Data Model? There is no much feature to operate and find the solution.