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>

 

[Aladin Akyurek]

INDEX/MATCH will behave the same way as VLOOKUP in the situation you describe.

1. Invoke Longre's INDIRECT.EXT. Caveats. (a) You need a 32bit machine; (b) Not sure if it admits (dynamic) named ranges.

2. Try Grove's PULL function.

3. See: http://www.mrexcel.com/forum/excel-...variable-cell-range-depending-cell-value.html

 

[shorn]

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.)

So i tested this by making a testing 1 (equal to your source book) and testing 2 (equal to what you want to import from the source) workbooks but had no issue with getting Vlook up to work when testing 1 was closed.

Few things i did notice though:
1: make sure you set your security to allow automatic updating of links.
2: The data from your source and what you are looking up is different. 009782720J in the source is not equal to 09782720J. Notice the second 0? that will fowl you up.

Index match is great because it allows you do a bit more complicated things than Vlookup. That being said i would just stay with Vlookup on something like this.

VLOOKUP(A2,'[test 1.xlsx]Sheet1'!$A$1:$B$8,2,)

Placed this in b2 of your import sheet and copied it down.

hope that helps..

 

[Lazarus416]

@shorn: I know this may seem like a silly question, but can you show me the Index/Match formula? When I try it using

=INDEX('[source_doc]Sheet1'!$B$2:$B$153, MATCH(A3,A2:A184,0))

instead of matching the value IN A3 and returning the corresponding data, it is using "A3" as the reference and is returning the corresponding data for that cell.

I tried using "0"& in the formula to account for the extra 0, but it did not help. I also tried manually changing the numbers to match (for testing purposes) just to see if it would work, but no change either. I can't figure out why it is using the A3 as the reference instead of using the value of A3 in the match function.

Master:

	A	B
2	009783860DA	=INDEX('[source_doc]Sheet1'!$B$2:$B$153, MATCH(A3,A2:A184,0))
3	02625910J
4	02705440J
5	02705450J

<tbody>
</tbody>

Source Doc:

	A	B
2	002625910J	6
3	002626200J	7
4	002705440J	6
5	002705450J	6
100	002705480J	6
153	009783860DA <tbody> </tbody>	100

<tbody>
</tbody>

If the Index/Match work correctly, I should be getting back an answer of 100, but instead I am getting an answer of 7 (the answer out of B3).

...but had no issue with getting Vlook up to work when testing 1 was closed.

Vlook works fine as long as you don't try and use a named (IE: dynamic) range in a closed workbook. If you use a named range, it won't work with the source closed. But if you use a non-named range, then you have to go in every time a row is added or removed from the source doc and update the destination formulas to reflect the change. Cumbersome if you have a lot of data...

 

[Aladin Akyurek]

...
Vlook works fine as long as you don't try and use a named (IE: dynamic) range in a closed workbook. If you use a named range, it won't work with the source closed. But if you use a non-named range, then you have to go in every time a row is added or removed from the source doc and update the destination formulas to reflect the change. Cumbersome if you have a lot of data...

Index/Match and VLookup are equivalent vis-a-vis dynamic names ranges and closed books...

 

[Lazarus416]

Meaning you can't use named ranges with Index/Match either?

 

[Lazarus416]

P.S. Just for kicks, I tried using Index/Match for looking up data based on names and got such vastly different results that I don't have a clue why it is retrieving what it is...its not even as simple as "using A3" instead of "value of A3". Now the results are just...well, I don't know why it is pulling the data it is.

	D	E
4	=INDEX('[source_doc]Sheet1'!$A$1:$A$200, MATCH(E4,E3:E185,0))	MORACE RAYMOND CHARLES

<tbody>
</tbody>

Source Doc:

	A	B
1	NAME	Test Data
2	SMITH OSCAR T	13
3	MORACE RAYMOND CHARL	20
4	ESCORPISO JONIE LYNN	20

<tbody>
</tbody>

• Match_type - Exact (0), Nearest Less Than (-1), or Nearest Greater Than (1) [optional]

If I use 0, I get back SMITH OSCAR T...if I use Nearest Less than (-1) I get SMITH OSCAR T...if I use Nearest Greater Than (1) I get GAGNON MICHELLE THI (a name from A90). I can't figure out what it is doing....(What I WANT is the corresponding data from Col B, but I am using Col A as the lookup array to make it easy to see when it does not work right.)

Ultimately, I am trying to figure out how to use a longer name to lookup inside a shorter version of it. IE: Use MORACE RAYMOND CHARLES as a reference to find the data next to MORACE RAYMOND CHARL.

 

[Aladin Akyurek]

Meaning you can't use named ranges with Index/Match either?

I tried saying that all the time. When the external workbook has dynamic named ranges, it won't help substituting Index/Match for Vlookup. Try rather the alternatives I already listed.

 

[shorn]

@shorn: I know this may seem like a silly question, but can you show me the Index/Match formula? When I try it using

=INDEX('[source_doc]Sheet1'!$B$2:$B$153, MATCH(A3,A2:A184,0))

instead of matching the value IN A3 and returning the corresponding data, it is using "A3" as the reference and is returning the corresponding data for that cell.

.....

Vlook works fine as long as you don't try and use a named (IE: dynamic) range in a closed workbook. If you use a named range, it won't work with the source closed. But if you use a non-named range, then you have to go in every time a row is added or removed from the source doc and update the destination formulas to reflect the change. Cumbersome if you have a lot of data...

So here is the index match statement

=INDEX('[source test.xlsx]Sheet1'!$B$1:$B$6,MATCH(A2,'[source test.xlsx]Sheet1'!$A$1:$A$6))

The problem you were having is that the info in the match has to be linked to the source doc and it has to have $$'s.

Second you can use named ranges without a problem. You just have to set the name ranges on the source doc.

=VLOOKUP(A2,'test.xlsx'!testrange,2,)

or

=INDEX('test.xlsx'!Indexrange,MATCH(A2,'test.xlsx'!Matchrange))

Both work fine if you define the name range on the source doc as follows..

Testrange=Sheet1!$A$1:$B$6
Indexrange=Sheet1!$B$1:$B$6
Matchrange=Sheet1!$A$1:$A$6

 

[Aladin Akyurek]

So here is the index match statement

=INDEX('[source test.xlsx]Sheet1'!$B$1:$B$6,MATCH(A2,'[source test.xlsx]Sheet1'!$A$1:$A$6))

The problem you were having is that the info in the match has to be linked to the source doc and it has to have $$'s.

Second you can use named ranges without a problem. You just have to set the name ranges on the source doc.

=VLOOKUP(A2,'test.xlsx'!testrange,2,)

or

=INDEX('test.xlsx'!Indexrange,MATCH(A2,'test.xlsx'!Matchrange))

Both work fine if you define the name range on the source doc as follows..

Testrange=Sheet1!$A$1:$B$6
Indexrange=Sheet1!$B$1:$B$6
Matchrange=Sheet1!$A$1:$A$6

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".