Looking up and capturing data in an Excel file based on variables

example.xlsx (9.4 KB)

What I’m looking to do is based on a value I’ve captured in another program (which is assigned to a variable), I want to use that to search the appropriate sheet and then column in an Excel File.

So using the attached worksheet as an example, I’ve captured ‘Samsung’ and ‘Memory’ to a variable

The Flow would then open up the attached Excel file, it would match ‘Samsung’ with the 1st variable and so it loads up the sheet relating to Samsung.
It then would utilise the second variable ‘Memory’ and so read ‘32GB’ and then I would open a browser and type in 32GB and then get text with what info I need i.e. price

Scratching my head with this one though so any help and advice would be most welcome

Hi @steve1977beyond !
Here is a suggestion: Lookup_variables_in_excel.xaml (16.5 KB)

In the initialization block, I hardcoded the values Samsung and Memory and filepath, but as you have variables you can just invoke this xaml and fill in the arguments as you want.

As I don’t know what is the currency, and what is the browser you use and navigator, I just kept it basic (respectively local one for currency, Chrome, Google).

Let us know if it does not work as expected or if I did not understand the need !

1 Like

That is absolute quality Hiba_B!! :smiley:

I did approach this in a different way - what is your opinion on how this was done and/or is one method more robust than the other?

Main.xaml (10.4 KB)

At the moment I have specified a value for the Variables but I’ll preceed this part with some instructions to get this data. The idea is then to ‘get text’ of values such as Description, but what I want to do is… if M2_TORX doesn’t return the values I want i.e. Description. How can I get it to then try M4_TORX? if M4_TORX returns no values I want it to then try M5_TORX and if that does find a value, I want to stop the loop on this part regardless of whether there is more data (i.e. M6_TORX)

To better visualise this, I have created an IF statement and so if the element exists on the web page, a message box will pop up.

However if the Element does not exist I have specified it to search a Column called “PNC02” - However, how can i get it to dynamically check PNC03, PNC04 etc. if the element does not exist in PNC02?
At the moment it’s hard coded to check PNC02, but is there any code I can put in the expression editor for Column Name that would give me more flexibility?

Main.xaml (17.3 KB)

okay, so now if the Element doesen’t exist, it then looks up the variable from the second lookup table - happy days.
But what if the second look up table doesen’t work…I then want it to search Lookup Table 3 and so on until it finds the element.

How would I do this?

Hi ! Sure, just gonna see first your xaml :grin: the best would be to make a list of your headers, then loop on it. Just gonna add this option to your code

Hi @steve1977beyond
Here is a suggestion: Main3.xaml (26.2 KB)
Let us know if it does not work as expected or if I did not understand the need !

Hi Hiba

You have been brilliant in replying and providing great examples, your conclusion is similar to what I did (didn’t see your reply when I got it done), but essentially if the Excel file had 6 columns, I created 6 Lookup Data Table scenarios.
Then using a series of nested IF Loops, I went through each one until it returned a value.

1 Like