VLOOKUP from right to left in closed workbook - not possible? Any alternative?

[mc84excel]

Sorry this is a long one. (You could jump to Question 1 but the Background info may help to understand what I am trying to achieve).


BACKGROUND:
I have a workbook which is data-connected to files from another program (I'll call this DATA WORKBOOK from now on). On a certain sheet, I have a list of employer names in Column B. In Column A I have a list of ID numbers (which are used to refer to an employer). I can't change the column order of this sheet as the data is connected to an external source and I don't want to risk compromising the data.

I am currently creating a form in another workbook (for sake of reference I'll call this PROJECT WORKBOOK). This form has code which looks up the Employer names in DATA WORKBOOK, and uses that to fill a list box.

Now for the problem. I need to somehow use the employer name (selected by the end user in the form in PROJECT WORKBOOK) to obtain the matching employer ID from DATA WORKBOOK.

But how do I do it?

QUESTION 1:
I was thinking of using a VLOOKUP on the closed DATA WORKBOOK. But AFAIK it is impossible to VLOOKUP when the matching data is not in the left most column. Unless someone knows otherwise? (i.e. I would have to return a value from Column A where the listbox.value matches data in Column B).

QUESTION 2:
Would it be possible to create an array from the DATA WORKBOOK, switch the columns in the array and then vlookup the array instead?

[abousetta]

"it is impossible to VLOOKUP when the matching data is not in the left most column".

One solution here [Tip #4] (and many other workarounds for common thing we want to do with VLookup but thought it wasn't possible).

Let me know if this resolves your issues.

abousetta

[mc84excel]

Let me know if this resolves your issues.

Wow. That formula is impressive. I never thought that would be possible.

However it is not working for me. I get a syntax error message when trying to run it.

Below is the code. The first intTemp doesn't work due to reading right to left. The second intTemp is based on tip 4 but it's not working?

Please Login or Register  to view this content.

[FDibbins]

As an alternative to the fancy vlookup, you can always use the index/match combination.

=index(range,row,column)
=index(range,match(what_you_want_to_find,column/range_to_search,0),match(what_you_want_to_find,roow/range_to_search,0))

[mc84excel]

Hello FDibbins

Thank you for your suggestion.

I am aware of the INDEX & MATCH combo as a superior alternative to VLOOKUP. But how would I get this to work in VBA to look up a closed workbook?

[FDibbins]

ok understood, good luck

[abousetta]

I'm not really good with worksheet formulas in vba. I've trying to make it work in vba but no luck. Works fine as in a worksheet but can't make the conversion work. Hopefully another member of the community can be of more help.

abousetta

[mc84excel]

I am marking the thread as as solved.

See this thread for the solution: http://www.excelforum.com/excel-prog...vba-array.html

[JosephP]

you can't use application.worksheetfunction with a closed workbook because you have no range object to pass it. your options would be to open the workbook or use a blank cell and actually enter a formula into it or perhaps use evaluate (although when you have to include a path it's real easy to exceed the 255 character limitation)