Alternatives to VLOOKUP?

[Lazarus416]

Ok, so I have been trying to build some spreadsheets that link to other workbooks so that when I open the "Master" spreadsheet, it does automatic VLOOKUPs and updates data from the others. But it doesn't work when the other workbooks are closed if I use table names. It only only works if it uses a range (not a name, but an actual range, A1:B10 or something). However, this would require manually updating the range anytime the source ranges changed. And that could get extremely tedious. In any case, is there anyway to "lookup" something in a different worksheet/table without using vlookup? (One that works with the source book closed.)

I can't just do a straight copy/paste as the references may occur more than once in the Master document. However, the imported data is specific to the reference. So 09782660J may show up 5 times, but it always equals 6. Any suggestions?

(I ran across a something about using an INDEX/MATCH combo, but I can't seem to figure out how to make that work in place of the VLOOKUP. That combo was =INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type)), but it doesn't seem to work the way the author says it would. You can find it at Say Goodbye to VLOOKUP, and Hello to INDEX-MATCH | eImagine Technology Group if you are interested in seeing it.)

09782660J	Need to import some data to here from another workbook
09782670J
09782680J
09782690J
09782700J
09782710J
09782720J
09782730J

<tbody>
</tbody>

Source Book:

003492100J	6
009782660J	6
009782670J	6
009782680J	5
009782690J	5
009782700J	6
009782710J	6
009782720J	5

<tbody>
</tbody>

 

[shorn]

I don't see how this helps: Names or just the range specs. They don't update when deletions and/or additions occur.

The OP states: "However, this would require manually updating the range anytime the source ranges changed. And that could get extremely tedious."

I interpret this as desiring a "dynamic set up".

The names specify a range so just have that range be large enough to encompass whatever source you want in that area.

Indexrange=Sheet1!$B:$B
Matchrange=Sheet1!$A:$A
testrange=Sheet1!$A$1:$B$900

 

[shorn]

I don't see how this helps: Names or just the range specs. They don't update when deletions and/or additions occur.

The OP states: "However, this would require manually updating the range anytime the source ranges changed. And that could get extremely tedious."

I interpret this as desiring a "dynamic set up".

To be fair i'm not really seeing the issue your bringing up Aladin. Doing the method i discribed above i had no issue having the data show up or not if it was added or deleted.

The OP states: "However, this would require manually updating the range anytime the source ranges changed. And that could get extremely tedious."

If you use the name from the source file you only ever have to change one area so its really not all that tedious.

For instance change what i posted above to this...

Indexrange=Sheet1!$B:$B
Matchrange=Sheet1!$A:$A
testrange=Sheet1!$A$1:$B$900

Then it should encompass whatever data you would ever put there. In addition if the import file is using the name from the source file you only ever have to change one area so its not really tedious at all.

One mistake i did noticed in my last post was you should have the match set to exact match (0)..You should also toss in some code to check for errors and whatnot.

Vlookup: =IFERROR(IF(A2="","",VLOOKUP(A2,'[source test.xlsx]Sheet1'!$A$1:$B$900,2,)),"Not Present")

Vlookup+name: =IFERROR(IF(A2="","",VLOOKUP(A2,'source test.xlsx'!testrange,2,)),"Not Present")

Index/match: =IFERROR(IF(A2="","",INDEX('[source  test.xlsx]Sheet1'!$B$1:$B$900,MATCH(A2,'[source  test.xlsx]Sheet1'!$A$1:$A$900,))),"Not present")

Index/match+name: =IFERROR(IF(A2="","",INDEX('source  test.xlsx'!Indexrange,MATCH(A2,'source test.xlsx'!Matchrange,0))),"Not  Present")

In testing this i would open the source file, add something in the A row and B row, close the source file and then open the import file. I had no issues with adding or deleting things from the source and having the data show up correctly. It would either show the correct data or it would show an error saying the data was not present.

Hope that helps

 

[Lazarus416]

I appreciate the suggestions. I'll take a look at first opportunity and see what I can do with it. Originally I WAS looking for a dynamic solution that would work with a closed source doc as I can't use named ranges across closed source docs, but after my last post I thought about it and realized that if I just make the range large enough to encompass any possible future row additions, then I would never have to go back and adjust them, even if the source doc adds a few new people. That will solve the dynamic and closed source doc issues. But I am still trying to figure out how to take a full name (Lazarus Michael Long) and look for it in a shortened lookup table (Lazarus Michael Lo) to import the connected data. I'll look later tonight and see if your suggestions solve that issue as well. (I have not had a chance to read in depth yet, just skim them.)

 

[shorn]

I appreciate the suggestions. I'll take a look at first opportunity and see what I can do with it. Originally I WAS looking for a dynamic solution that would work with a closed source doc as I can't use named ranges across closed source docs, but after my last post I thought about it and realized that if I just make the range large enough to encompass any possible future row additions, then I would never have to go back and adjust them, even if the source doc adds a few new people. That will solve the dynamic and closed source doc issues. But I am still trying to figure out how to take a full name (Lazarus Michael Long) and look for it in a shortened lookup table (Lazarus Michael Lo) to import the connected data. I'll look later tonight and see if your suggestions solve that issue as well. (I have not had a chance to read in depth yet, just skim them.)

The thing with named ranges is that they only work for the document that they were created for. The key to using them across closed source docs is to use the name you want from the document your linking to.

So you set up a name in the source file. Then in the import file you call the source file with the name you want to use. For instance

['source test.xlsx'!Indexrange]

. The first part is the name of the source file and the second is the name you want to use. The power of this is that if i have say 50 people with 50 different documents connecting to my source file using Indexrange i only have to change the range in my indexrange and it effects all of the people the same (they might have to quite out and load up their file again though..)

As to your second question i guess it depends on your structure. You could do something simple like this..

A	B	C
Lazarus Michael	Lazar	MATCH(LEFT(B1,5)&"*",$A$1:$A$31,0)
Tiera Havel	Tiera K*	VLOOKUP(B2,$A$1:$A$31,1,FALSE)
Damien Kyer	Tiera K*	MATCH(B3,$A$1:$A$31,0)

<tbody>
</tbody>

The code in C1 is just looking at the first 5 letters you have typed into B1. It will ignore any letters after 5 and only show the first name it finds like this. So if you have to Damien's it will find the first one. Like wise if you type in Tiera Kyer it will say she is in row 2 of the array.

You could also search for wildcards with the *. This allows you to type as much or as little of the name as you would like. The more you type the more accurate the search will be. Again though it does not care about duplicates and will return the first one. You could then create a dropdown list that took into account the search you were doing in b2 and listed the people who fit those criteria so you can load data specific to them.

 

[Lazarus416]

The thing with named ranges is that they only work for the document that they were created for. The key to using them across closed source docs is to use the name you want from the document your linking to.

So you set up a name in the source file. Then in the import file you call the source file with the name you want to use. For instance
Code:

['source test.xlsx'!Indexrange]
. The first part is the name of the source file and the second is the name you want to use. The power of this is that if i have say 50 people with 50 different documents connecting to my source file using Indexrange i only have to change the range in my indexrange and it effects all of the people the same (they might have to quite out and load up their file again though..)

Shorn, I HAVE done that with my named ranges, but they never work and all the research I have done says they won't work across a closed workbook no matter what you do. Here is the VLOOKUP I was trying to use originally...

=IF(A5="","N/L",VLOOKUP("0"&A5,'source_file.xlsx'!named_range,2,FALSE))

But it only works if the source workbook is open. My research says this is because a named range cannot be used with a closed source.


As far as using the names, I'll have to tinker with what you have down and see if I can make it work. VLOOKUP is no good because the reference name is longer than the names in the lookup array and so the "precise match" won't find it and the "closest match" won't return the right data. I don't know if I can find a combo function that will let me do something similar to the VLOOKUP or not.

 

[Lazarus416]

P.S. My Lookup issue with names is detailed out a little better here: http://www.mrexcel.com/forum/excel-...up-using-contains-criteria-2.html#post3641494 .

 

[Aladin Akyurek]

Shorn, I HAVE done that with my named ranges, but they never work and all the research I have done says they won't work across a closed workbook no matter what you do. Here is the VLOOKUP I was trying to use originally...

=IF(A5="","N/L",VLOOKUP("0"&A5,'source_file.xlsx'!named_range,2,FALSE))

But it only works if the source workbook is open. My research says this is because a named range cannot be used with a closed source.

That's why I listed 3 suggestions...

As far as using the names, I'll have to tinker with what you have down and see if I can make it work. VLOOKUP is no good because the reference name is longer than the names in the lookup array and so the "precise match" won't find it and the "closest match" won't return the right data. I don't know if I can find a combo function that will let me do something similar to the VLOOKUP or not.

The following sets...

VLOOKUP("*"&A2,F2:G10,2,0)
VLOOKUP(A2&"*",F2:G10,2,0)
VLOOKUP("*"&A2&"*",F2:G10,2,0)

INDEX(G2:G10,MATCH("*"&A2,F2:F10,0))
INDEX(G2:G10,MATCH(A2&"*",F2:F10,0))
INDEX(G2:G10,MATCH("*"&A2&"*",F2:F10,0))

are exactly equivalent.

 

[shorn]

Shorn, I HAVE done that with my named ranges, but they never work and all the research I have done says they won't work across a closed workbook no matter what you do. Here is the VLOOKUP I was trying to use originally...

=IF(A5="","N/L",VLOOKUP("0"&A5,'source_file.xlsx'!named_range,2,FALSE))

http://office.microsoft.com/en-us/e...ll-range-in-another-workbook-HP010342364.aspx

When the source is not open in Excel, the external reference includes the entire path.
External reference
=SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)

Here are the two files i used for the testing of this question. I make a change to the source file, close it, and the change is reflected in the import from source file. Its about as dynamic as you could want really.

https://dl.dropboxusercontent.com/u/1589284/D&D stuff/Excel stuff/import from source test.xlsx
https://dl.dropboxusercontent.com/u/1589284/D&D stuff/Excel stuff/source test.xlsx

*Note: Make sure you set your security to allow updating of the links or it wont work for yeh.

As far as using the names, I'll have to tinker with what you have down and see if I can make it work. VLOOKUP is no good because the reference name is longer than the names in the look up array and so the "precise match" won't find it and the "closest match" won't return the right data. I don't know if I can find a combo function that will let me do something similar to the VLOOKUP or not.

As i posted posted above and Aladin Akyurek posted, your best bet is to use wild cards. Either you input the wildcard where you want in the input cell or your formula places the wildcard for you.

Wildcards allow you to type in part of something and then have the formula look for the rest of the string. For instance typing in Lazarus*,*Michael*, and *hael Long will find Lazarus Michael Long.

Here's a quick name thing for you as well. It lists the 4 basic ways you can search with wildcards.

https://dl.dropboxusercontent.com/u/1589284/D&D stuff/Excel stuff/Name Lookup.xlsx

Hope this helps

 

[shorn]

P.S. My Lookup issue with names is detailed out a little better here: http://www.mrexcel.com/forum/excel-...up-using-contains-criteria-2.html#post3641494 .

Answered you in that thread.

 

[jsotola]

here is a way to do SQL query

it runs without opening source file in excel

you can use the WHERE clause in the query string to retrieve only the data you need

Sub doSQL()

    Dim i As Integer
    
    Dim strCon As String
    Dim mySqlQuery As String
    
    ' refer to 'microsoft activex data objects library'
    Dim cn As Object
    Dim rs As Object
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")


    ' this one gets data from this workbook
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source='" & ThisWorkbook.FullName & "';" & _
             "Extended Properties='Excel 12.0;HDR=No;IMEX=1';"


    ' data comes from test.xlsm that is in same folder as this workbook
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source='" & ThisWorkbook.Path & "\test.xlsm';" & _
             "Extended Properties='Excel 12.0;HDR=No;IMEX=1';"


    cn.Open strCon  ' open database connection
    
    ' SQL query string
    mySqlQuery = "SELECT * FROM [Sheet1$A1:c10] ORDER BY 1"     ' data sorted on 1st column
    
    mySqlQuery = "SELECT * FROM [Sheet1$A1:c10]"                ' data as is
    mySqlQuery = "SELECT * FROM [rangeName]"                    ' using range name
    
    rs.Open mySqlQuery, cn                                      ' run query
        
    Sheets("Sheet2").Range("B2").CopyFromRecordset rs           ' copy result to worksheet
    
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing




End Sub