Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Building Flows / VLOOKUP, XLOOKUP or IN...
Power Automate - Building Flows
Answered

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

(0) ShareShare
ReportReport
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:

blinabj_0-1676363407980.png

 

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

blinabj_2-1676364096951.png

 

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:

blinabj_1-1676363546856.png

 

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

 

I really appreciate the help!

Categories:
Business Process Flows
  • blinabj Profile Picture
    blinabj 159 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

    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:

    blinabj_1-1703668562529.png

     

    blinabj_0-1703668442056.png

     

    Hope this helps😊

  • ShineTan123 Profile Picture
    ShineTan123 213 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

    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()'
			)
		)
	)
)

     

  • blinabj Profile Picture
    blinabj 159 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

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

  • Chriddle Profile Picture
    Chriddle 7,922 Super User 2025 Season 1 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

    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()'
			)
		)
	)
)

     

     

     

     

     

  • blinabj Profile Picture
    blinabj 159 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

    @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>
     
    blinabj_0-1676387887671.png

     

  • Chriddle Profile Picture
    Chriddle 7,922 Super User 2025 Season 1 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

    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? 

  • blinabj Profile Picture
    blinabj 159 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

    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:

    blinabj_0-1676382449608.png

    blinabj_2-1676382726032.png

     

    blinabj_1-1676382595553.png

     

     

  • Verified answer
    Chriddle Profile Picture
    Chriddle 7,922 Super User 2025 Season 1 on at
    Re: VLOOKUP, XLOOKUP or INDEX MATCH on two columns using Power Automate Flow

     

    Chriddle_0-1676376267127.png

    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"
 }
]

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 1

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans