Executing VLookup function using Power Automate flow

[freshyshreya]

Hi,

Can we do V lookup in excel using power automate flow?

[DAllen365]

I think you're going to need to expand a little more about what exactly you are trying to do.  Are you reading an existing Excel file or creating a new Excel file and dumping data to it?  The Excel actions in Flow are limited as to what they do, but you can write expressions to do almost anything with the data itself depending what you are doing.  

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

[freshyshreya]

Hi @DAllen365 ,

I have 2 excel files containing data as shown in the below pic.

I want to create a power automate flow where it compares the values of the rows of one excel file(taking 4 columns into account i.e. Category,Description,Link & Demo Account) with that of the other excel and if it matches,it should change the value of the status column(marked) as rediscovered.

[Anonymous]

I have the same question. Is there a solution for this?

[123213123]

anyone can help with this? i need also need it

[YvoValcon]

Me too!

[DAllen365]

You don't need Power Automate to do this, you can do this natively in Excel on the fly.  I now prefer to use XLOOKUP instead of VLOOKUP, has many advantages and is the successor to VLOOKUP (assuming you have Office 365 or 2019 Excel).  For testing I just used 2 sheets in one workbook, but you could easily tweak to be in separate workbooks (or you could paste the data into one workbook).  Let's say you want to take your example, and look for the first 4 row values from sheet 1 (columns A-D for row 2) in columns (A-D) of sheet 2 and if found, return "Match".  For this to work, the return value has to be an array so in a neighboring column (K in mine), paste a whole column of the word you want to return (Match in my case).  But if it's not found, you can type text for that.  So you could put column K on the sheet 2 data, up to you.  For the main formula, in your sheet 1 Status column, paste this formula:

=XLOOKUP(A2&B2&C2&D2,Sheet2!A:A&Sheet2!B:B&Sheet2!C:C&Sheet2!D:D,K:K,"not found")

This is saying on sheet 1, take columns B1, B2, B3 and B4 and look for matches in columns A, B, C and D in sheet 2 and return the value in K2, otherwise write "not found".  Hope that helps!

Doug
----------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[YvoValcon]

Hi @DAllen365 ,

Thanks for your reply. I do know how to do it in excel, I use index match myself. But for my specific case I want to merge data from two excel files (which are exported via Power Automate), but the data must be merged into the right row (i.e by using indexmatch or vlookup).  

[bullmau185]

The key to achieving this is that you need a unique identifier. I understand the below example isn't exactly as was asked but the same logic applies. I first Initialize a String variable of Rediscovered then List the Rows from one of the Excel Sheets. From there, run an Apply to Each on the body/value of the List Rows outputs. Then use a Get row pointing to the second Excel sheet with the unique identifier (In this example I am using the IssueID column). Still within the Apply to Each, run a Condition to check if each column matches per row. In this example, when a value within a row doesn't match, I am updating the Status value to Rediscovered in the First Excel Sheet only. 

This type of flow can be set to run daily, for example, to verify everything matches.