VLOOKUP from 2 Excel and write the Status.

(1) Share

Report

Posted on by nraindmia

Hi, I'm new at Power Automate Desktop,

You can refer to Flowchart, I hope you can understand this well. I already created the power automate desktop flow.

But I stuck at the VLOOKUP and Condition process:

1)I already do a CONCAT function to combine Company name/Legal Entity and Employee No/Person Number to a new column called as UniqueID for both Excel1 and Excel2.
2) Need help for this part to VLOOKUP UniqueID from Excel 1 and Excel 2
3) and when the uniqueID form excel 1 and excel 2 from VLOOKUP is match, then write the status as "Matched"
3) If the UniqueID is different, put the status as "Transferred"
4) If in excel 1 have a date in Termination Date, put the status as "Terminated". Usually termination date will be blank unless they got terminated.

how to do the VLOOKUP from Excel 1 to Excel 2 to make sure the data is "Matched", "Transferred" or "Terminated"? Is my flow is correct?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

trice602 15,402 Super User 2025 Season 2 on at

Like (0)

Report
Hi @nraindmia ,

I really like what you have built here! Great job! I'm sharing an Excel formula with you and hope it works for you! When I looked at your questions and then started building a formula that would work, this is what I came up with based on what I could assume for the post.

=IF(Book1Excel1.xlsx!Table1[@TerminatedDate]<>"","Terminated",XLOOKUP(A2,Book1Excel1.xlsx!Table1[UniqueID],Book1Excel1.xlsx!Table1[HelperCol1],"Transferred"))

The key here, and a common best practice, is to implement a "helper column". This can be a column off to the far right and typically it is hidden because it will only be used for formulas and not visual use. There are most likely other ways to do this to but I'm suggesting the is the easy route.

The first part of the formula simply checks if there is a terminated date, true or false, "Terminated". The XLOOKUP, checks the UniqueID and if they match, "Matched", else (the only option left) "Transferred".

Was this reply helpful? Yes No
nraindmia on at

Like (0)

Report

Hi, I really appreciate it! This can be use in excel but currently for now, i need the formula in Power Automate Desktop. Thank you.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report

This seems to be a duplicate topic and I have already provided a suggested solution here: https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Concat-2-column-to-1-column-in-first-and-second-excel-then/m-p/2303452#M30036

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No