Forum Discussion

Bilal_321321's avatar
Bilal_321321
Helper I
9 years ago
Solved

VLookup help

Hi,

 

I need some help with Power BI pro. I am trying to replicate a vlookup we used in excel which basically is this:


=IFERROR(VLOOKUP(B3*1,Mapping!$P:$Q,2,FALSE),"No Group")

 

I have been able to use Column 1 = RELATED('Report 2'[Group]) which pulls the correct field but on instances where there is no match I want it to display "No Group"

 

Thanks

Need Help
  • MFelix's avatar
    MFelix
    9 years ago

     

    Hi Bilal_321321, 

     

    the Lookupvalue formula returns blank if it doesn't have a match in the lookup (lookpvalue formula) try changing your formula to this:

     

    Column = SWITCH (
    TRUE (),
    ISBLANK (
        LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
    ), "NoGroup",
    LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
)

     

    Regards,

    MFelix

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi Bilal_321321,

     

    You need to do a formula with the following sintax:

     

     

    Column =
IFERROR (
    LOOKUPVALUE ( Table[Result], Table[Search_Value], "Value_to_Search" ),
   "No Group"
)

     

    Regards,

    MFelix

     

    • Bilal_321321's avatar
      Bilal_321321
      Helper I
      9 years ago

      Hi MFelix,

      I must be doing something wrong, the LOOKUPVALUE is working but for instances it's not got a value the "No Group" is not populating. This is the formula i have created so far:

       

      Column = IFERROR ( LOOKUPVALUE('Report 2'[Group],'Report 2'[GroupID],'Report1'[GroupID]) , "No Group")

       

      The 'Report 2'[Group] is the table and column I'm looking at, 'Report 2'[GroupID] is the linked field I am searching based on 'Report'[GroupID] and the "No Group" should appear instead of blanks.

       

      I'm probably making an obvious mistake but can't see it.

       

      Thanks

      Bilal

       

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

         

        Hi Bilal_321321, 

         

        the Lookupvalue formula returns blank if it doesn't have a match in the lookup (lookpvalue formula) try changing your formula to this:

         

        Column = SWITCH (
    TRUE (),
    ISBLANK (
        LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
    ), "NoGroup",
    LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
)

         

        Regards,

        MFelix