vlookup - lookup with an array in another workbook?

[Cobaum]

I am trying to lookup values in one workbook from an array in another
workbook. Can this be done? I am getting #N/A errors from a perfectly good
vlookup formula. I am assuming that it does not like the second workbook.
If that is an incorrect assumption, let me know what else I should check.
Here's the formula for those that want to check it: =VLOOKUP(A6,'[2008
Projection.xls]08 PROJ'!A$9:C$191,3). Thanks in advance.

 

[ShaneDevenshire]

Hi,

Not sure what you mean by "with an array"

The following formula works just fine.

=VLOOKUP(A1,tblBookSales.xls!$A$1:$E$101,3,FALSE)

Note you are doing an approximate match so the lookup column, the first
column in the lookup table must be sorted in Ascending order.

 

[Max]

=VLOOKUP(A6,'[2008 Projection.xls]08 PROJ'!A$9:C$191,3)

As-is, you need to ensure that the values in the table array's lookup col
are sorted in ascending order

Alternatively, amend it for exact matching, viz.:
=VLOOKUP(A6,'[2008 Projection.xls]08 PROJ'!A$9:C$191,3,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Cobaum]

On my Excel it calls it a "table_array" meaning lookup table. What do you
mean by an "approximate match"? How do I change the formula to pick up
values if the lookup column is not in numerical order? Thanks.

Hi,

Not sure what you mean by "with an array"

The following formula works just fine.

=VLOOKUP(A1,tblBookSales.xls!$A$1:$E$101,3,FALSE)

Note you are doing an approximate match so the lookup column, the first
column in the lookup table must be sorted in Ascending order.

--
Cheers,
Shane Devenshire

I am trying to lookup values in one workbook from an array in another
workbook. Can this be done? I am getting #N/A errors from a perfectly good
vlookup formula. I am assuming that it does not like the second workbook.
If that is an incorrect assumption, let me know what else I should check.
Here's the formula for those that want to check it: =VLOOKUP(A6,'[2008
Projection.xls]08 PROJ'!A$9:C$191,3). Thanks in advance.

 

[satadru]

Dear All,

I have had accurate results with INDEX and MATCH functions combined. The
best part is, the data need not be in ascending order. Regardless of the
arrangement of data, one can easily look up the values with ease.

Best regards,
Satadru

=VLOOKUP(A6,'[2008 Projection.xls]08 PROJ'!A$9:C$191,3)

As-is, you need to ensure that the values in the table array's lookup col
are sorted in ascending order

Alternatively, amend it for exact matching, viz.:
=VLOOKUP(A6,'[2008 Projection.xls]08 PROJ'!A$9:C$191,3,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

I am trying to lookup values in one workbook from an array in another
workbook. Can this be done? I am getting #N/A errors from a perfectly good
vlookup formula. I am assuming that it does not like the second workbook.
If that is an incorrect assumption, let me know what else I should check.
Here's the formula for those that want to check it: =VLOOKUP(A6,'[2008
Projection.xls]08 PROJ'!A$9:C$191,3). Thanks in advance.

 

[ShaneDevenshire]

Hi,

If its not sorted then you must use an exact match - that means the 4th
argument in my example would be TRUE.

Note: For exact matches the table does not need to be sorted on the lookup
column, but for approximate matches it does.

--
Cheers,
Shane Devenshire

On my Excel it calls it a "table_array" meaning lookup table. What do you
mean by an "approximate match"? How do I change the formula to pick up
values if the lookup column is not in numerical order? Thanks.

Hi,

Not sure what you mean by "with an array"

The following formula works just fine.

=VLOOKUP(A1,tblBookSales.xls!$A$1:$E$101,3,FALSE)

Note you are doing an approximate match so the lookup column, the first
column in the lookup table must be sorted in Ascending order.

--
Cheers,
Shane Devenshire

I am trying to lookup values in one workbook from an array in another
workbook. Can this be done? I am getting #N/A errors from a perfectly good
vlookup formula. I am assuming that it does not like the second workbook.
If that is an incorrect assumption, let me know what else I should check.
Here's the formula for those that want to check it: =VLOOKUP(A6,'[2008
Projection.xls]08 PROJ'!A$9:C$191,3). Thanks in advance.

 

[Max]

.. best part is, the data need not be in ascending order.

Not necessarily. Like VLOOKUP, MATCH, in the INDEX/MATCH, must be set for an
exact match in order for the above to hold true, eg: MATCH(5,B2:B10,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---