VLOOKUP from 2 Excel and write the Status.

Like (1) Share

Report

Posted on 20 Aug 2023 07:31:18 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?

All responses (3)

Answers (0)

Agnius Bartninkas 10,045 Most Valuable Professional on 21 Aug 2023 at 04:44:21

Like (0)

Report

Re: VLOOKUP from 2 Excel and write the Status.

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.
nraindmia on 21 Aug 2023 at 02:38:57

Like (0)

Report

Re: VLOOKUP from 2 Excel and write the Status.

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.
trice602 13,768 Super User 2025 Season 1 on 20 Aug 2023 at 17:39:48

Like (0)

Report
VLOOKUP from 2 Excel and write the Status.

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".