Reading from a VERY large excel spreadsheet

[jharrity]

Hi. I have a 2 column excel spreadsheet that has over 17,000 rows.  I want to use a variable to identify a particular row of column A (the row that has a value that matches the value of the variable), and then read the value from column B for that particular row.  Currently, I have a for each loop that reads from the first row, compares the value to the variable value, if they don't equal each other, then we read the next row, and so on until we find a match.  When it matches, we read the value from column B.  As you can imagine, this is taking forever to find that single row that has a value that matches the variable value.  

How can I do this more quickly using Power Automate Desktop?  Any help would be GREATLY appreciated.

[Anonymous · Accepted solution]

Hi @jharrity 

It's not beautiful by any means, but it's waaay quicker than loop and works (tested) 😅:

1) 'Launch Excel' and choose your file

2) 'Get window' > foreground window (gives %AutomationWindow% as output variable)

3) 'Focus window' > Find window mode = By window instance/handle, Window instance = %AutomationWindow%

(Steps 2-3 are to assure PAD is with Excel window active and focused)

4) 'Write to Excel worksheet' > write in cell D1 (as you said your ws has only columns A and B filled) the value to be find in Column A 

5) 'Send keys' > {Down}, {F2}

6) 'Send keys' > =VLOOKUP(D1,$A:$B,2,0) {Enter}

7) 'Read from Excel worksheet > get value in D2 cell

[jharrity]

Thanks! That worked. The lookup is lightening quick now.