Power Query Lookup range changed based on Cell value

[Negi1984]

Hi All,

I have a Master sheet having wk number mentioned in first column. I want to apply vlookup on name based on value mentioned in Wk number column.

For example :- if wk number is less than 26 than vlookup apply from Group 1 sheet and pick the "Current Hrs" value based on Resource Name otherwise from Group 2 sheet.

can anybody suggest me , how dynamically we can change the lookup table and pick the value.

Below is the sample data.

https://1drv.ms/x/s!Ap80Ku6M2Tw5gTyVsOwG-9-qNL-2

Regards,
Rajender

[v-yulgu-msft · Accepted solution]

Hi @Negi1984,

It would be much easier to achieve that via DAX. Please new a calculated column in Master sheet:

Column =
IF (
    'Master Sheet'[Week no] < 26,
    LOOKUPVALUE (
        Group1[Current Hrs],
        Group1[Resource Name], 'Master Sheet'[Resource Name]
    ),
    LOOKUPVALUE (
        Group2[Current Hrs],
        Group2[Resource Name], 'Master Sheet'[Resource Name]
    )
)

However, if you want to do that via Power Query, you may need to create a custom VLOOKUP function.

Best regards,

Yuliana Gu

[Negi1984]

Thanks a lot Yuliana for your valuable support.