VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

(0) Share

Report

Posted on by blinabj

159

Hi,

Is there any way to create something similar to a VLOOKUP, XLOOKUP or INDEX MATCH on two columns by using Power Automate Flow (or perhaps SharePoint Lists)?

I have way too many rows to use the conditioning tool in flow, unless I can make it dynamic in a way? Or there is another better component?

Here is a small example of my problem - but remember, I have so many rows that everything needs to be dynamic!

I have this SharePoint list, List1:

And I have this SharePoint list, List2, (this one can be in Excel if that is better):

Then based on the color and toy in List1, I need to get their favorite candy from List2, so that it ends up looking like this:

Note! Their favorite candy is decided based on both their favorite color and toy.

I really appreciate the help!

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

Chriddle 8,436 Super User 2025 Season 2 on at

Like (1)

Report
Select
From: @{outputs('Person')}
Map:
addProperty( item(), 'Favorite candy', first( xpath( xml(json(concat('{"root":{"item":', outputs('Candy'),'}}'))), concat( '//item[Favorit_x0020_color="', item()['Favorit color'], '" and Favorit_x0020_toy="', item()['Favorit toy'], '"]/Favorite_x0020_candy/text()' ) ) ) )
Outputs:
[ { "PersonID": 1, "Name": "Nora", "Favorit color": "Pink", "Favorit toy": "Cars", "Favorite candy": "Chocolate" }, { "PersonID": 2, "Name": "Tim", "Favorit color": "Green", "Favorit toy": "Lego", "Favorite candy": "Gummi worms" }, { "PersonID": 3, "Name": "Kim", "Favorit color": "Pink", "Favorit toy": "Lego", "Favorite candy": "Sour patch kids" } ]

Was this reply helpful? Yes No
blinabj 159 on at

Like (0)

Report

Thank you so much @Chriddle ! This is exactly what I need, but for some reason it doesn't work when I get the data from SharePoint lists (I get "null"). Do you understand why it doesn't work and what I can do?

This is my flow:

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (1)

Report
Hard to say 😉
Can you add a Compose with expression
xml(json(concat('{"root":{"item":', outputs('Candy'),'}}')))
at the end of your flow and share the output?

Was this reply helpful? Yes No
blinabj 159 on at

Like (0)

Report

@Chriddle, thanks for helping me! The output is:

<root><item><Favorit_x0020_candy>Chocolate</Favorit_x0020_candy><Favorit_x0020_color>Pink</Favorit_x0020_color><Favorit_x0020_toy>Cars</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Chocolate</Favorit_x0020_candy><Favorit_x0020_color>Pink</Favorit_x0020_color><Favorit_x0020_toy>Teddy bears</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Sour patch kids</Favorit_x0020_candy><Favorit_x0020_color>Pink</Favorit_x0020_color><Favorit_x0020_toy>Lego</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Sour patch kids</Favorit_x0020_candy><Favorit_x0020_color>Yellow</Favorit_x0020_color><Favorit_x0020_toy>Cars</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Sour patch kids</Favorit_x0020_candy><Favorit_x0020_color>Yellow</Favorit_x0020_color><Favorit_x0020_toy>Lego</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Gummy worms</Favorit_x0020_candy><Favorit_x0020_color>Yellow</Favorit_x0020_color><Favorit_x0020_toy>Teddy bears</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Gummy worms</Favorit_x0020_candy><Favorit_x0020_color>Green</Favorit_x0020_color><Favorit_x0020_toy>Lego</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Lolly pop</Favorit_x0020_candy><Favorit_x0020_color>Green</Favorit_x0020_color><Favorit_x0020_toy>Cars</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Lolly pop</Favorit_x0020_candy><Favorit_x0020_color>Green</Favorit_x0020_color><Favorit_x0020_toy>Teddy bears</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Lolly pop</Favorit_x0020_candy><Favorit_x0020_color>Blue</Favorit_x0020_color><Favorit_x0020_toy>Cars</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Snickers</Favorit_x0020_candy><Favorit_x0020_color>Blue</Favorit_x0020_color><Favorit_x0020_toy>Teddy bears</Favorit_x0020_toy></item><item><Favorit_x0020_candy>Snickers</Favorit_x0020_candy><Favorit_x0020_color>Blue</Favorit_x0020_color><Favorit_x0020_toy>Lego</Favorit_x0020_toy></item></root>

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (1)

Report
My bad, I spelled "Favorite candy" (in Candy and in Select's Map). 😞
Fixed Select's Map for your data:

addProperty( item(), 'Favorit candy', first( xpath( outputs('Xml'), concat( '//item[Favorit_x0020_color="', item()['Favorit color'], '" and Favorit_x0020_toy="', item()['Favorit toy'], '"]/Favorit_x0020_candy/text()' ) ) ) )

Was this reply helpful? Yes No
blinabj 159 on at

Like (0)

Report

Thank you so much for all the help @Chriddle ! My mistake to misspell it in the first place 😅

Was this reply helpful? Yes No
ShineTan123 213 on at

Like (0)

Report
Hi @blinabj,
Would you mind sharing the detail of your coding? I have the same problem as had.
I read through the post but I don't know which are using the hard coded which are using dynamic content, for example, these should come from dynamic content? Hope to get your reply. Thanks!
item()['Favorit color'], '" and Favorit_x0020_toy="',

addProperty( item(), 'Favorit candy', first( xpath( outputs('Xml'), concat( '//item[Favorit_x0020_color="', item()['Favorit color'], '" and Favorit_x0020_toy="', item()['Favorit toy'], '"]/Favorit_x0020_candy/text()' ) ) ) )

Was this reply helpful? Yes No
blinabj 159 on at

Like (0)

Report

Hi @ShineTan123 ,

This is the code I am currently using:

addProperty(
    item(),
    ' Salary',
    first(
        xpath(
            xml(json(concat('{"root":{"item":', outputs('Salary'),'}}'))),
            concat(
                '//item[JobTitle="',
                item()['JobTitle'],
'" and City="',
                item()['City'],
                '" and Department="',
                item()['Department'],
                '"]/Salary/text()'
            )
        )
    )
)

You must edit anything in bold to your needs.
This is a double index match that looks at both City and Department.

This is how my flow looks:

Hope this helps😊

Was this reply helpful? Yes No