Hi,
I have some formula's that I am trying to replace using Power Query.... not sure what the best way to explain this is but I will explain the scenario in excel first. I need to look at column 1 and return the value specified in the lookup table, but if the number doesn't exist in column 1 lookup table, I want to lookup column 2 and return the value based on the lookup table. The formula I am using to do this is: =IFNA(VLOOKUP(A2,A10:C15,3,0),VLOOKUP(B2,B10:C15,2,0))
EXAMPLE DATA with last column being what i want to be generated by the formula:
<tbody>
</tbody>
LOOKUP TABLE:
<tbody>
</tbody>
Does anyone have any ideas how to do this in Power Query? And can Power Query return a certain value if no matches are received at all? I know how to merge tables as a replacement for standard vlookups but have no idea how to do a complicated lookup. Any help would be greatly appreciated!!!!!
I have some formula's that I am trying to replace using Power Query.... not sure what the best way to explain this is but I will explain the scenario in excel first. I need to look at column 1 and return the value specified in the lookup table, but if the number doesn't exist in column 1 lookup table, I want to lookup column 2 and return the value based on the lookup table. The formula I am using to do this is: =IFNA(VLOOKUP(A2,A10:C15,3,0),VLOOKUP(B2,B10:C15,2,0))
EXAMPLE DATA with last column being what i want to be generated by the formula:
Column 1 | Column 2 | RETURNED VALUES |
1 | d | blue |
2 | e | red |
3 | f | yellow |
4 | d | green |
5 | e | orange |
6 | f | black |
<tbody>
</tbody>
LOOKUP TABLE:
Column 1 | Column 2 | Result |
1 | blue | |
2 | red | |
3 | yellow | |
d | green | |
e | orange | |
f | black |
<tbody>
</tbody>
Does anyone have any ideas how to do this in Power Query? And can Power Query return a certain value if no matches are received at all? I know how to merge tables as a replacement for standard vlookups but have no idea how to do a complicated lookup. Any help would be greatly appreciated!!!!!