Search excel file, return value from different column

[buickgn]

I've tried searching the Excel board and this board, but haven't found anything that can help yet. I have an Excel file that has three columns. I want to search the first column for a value (1-99), then return the value from the same row, but from the 3rd column. Pretty much the same as the VLookup function, but I haven't found a way to do it in LV. Here's a basic example:

Column1 | Column 2 | Column 3

01              PSIG         3

.

.

20              PSIS         2

So, if "20" is entered, it will return the value "2".

Thanks again

[buickgn]

I was able to find a small VI that finds the address of the value that I'm looking for. For example, if I search for "05" it will tell me the column and row it's in. However, I want it to return the value that is in the same row, but different column. I'm not sure which ActiveX control to use for this.

Here's an image of the VI:

[ben64 · Accepted solution]

---

@buickgn wrote:

 Pretty much the same as the VLookup function, but I haven't found a way to do it in LV. 

Thanks again

---

Well, use the VLookup function then, from _Application WorksheetFunction (pass a range reference to Argument 2).

Ben64

[buickgn]

Hi Ben64,

Is it possible to do this using a different function? For example, in the screenshot I posted it requires only one input, "what" to find. I don't want the user to have to input anything else if at all possible. Would it be a better idea to not use an Excel file and use a CSV file?

[ben64]

---

@buickgn wrote:

Hi Ben64,

Is it possible to do this using a different function? For example, in the screenshot I posted it requires only one input, "what" to find. I don't want the user to have to input anything else if at all possible. Would it be a better idea to not use an Excel file and use a CSV file?

---

Why use a different function if VLookup do exactly what you want? You can show / hide or change to constant anything you want, leave it to the user to enter a value in the look for control only.

Ben64

[buickgn]

Is it possible to use the Application WorksheetFunction in place of the two "Range" invoke nodes in my screenshot? If so, what do you have the Application WorksheetFunction's "reference" wired to? Sorry, I'm still not sure how to wire this to get the result.

[ben64]

Is there an Application reference output to the Open Excel vi you're using. If yes use it for the _Application WorksheetFunction reference. Also, the reference should be closed in the reverse order they were opened. You should change this at the end of the vi picture you are showing. (no you can't use the Application WorksheetFunction in place of the two "Range" invoke nodes in your screenshot)

Ben64

[buickgn]

There is an Application reference output. If I can't use the Application WorksheetFunction in place of the two "Range" invoke nodes, where do you put the code in your picture? Is it possible to post the code in your picture so I can modify the controls with constants as needed?

[ben64]

Look For is what you are looking for in the first column, you can change the string control to a numeric control if you are looking for a numeric value. Column is the column number of the value you want to output (column A -> 1, column B -> 2, ...). Replace range_lookup by a boolean False constant.

Ben64

[buickgn]

Here is what I have now. It works and gives me the correct value. However, after it runs it seems to be holding the file in memory and it only opens in read-only. How can I make this release the file?