Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup - lookup with an array in another workbook?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup - lookup with an array in another workbook?
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 "Cobaum" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup - lookup with an array in another workbook?
=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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup - lookup with an array in another workbook?
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. "ShaneDevenshire" wrote: 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 "Cobaum" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup - lookup with an array in another workbook?
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 "Max" wrote: =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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup - lookup with an array in another workbook?
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 "Cobaum" wrote: 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. "ShaneDevenshire" wrote: 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 "Cobaum" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup - lookup with an array in another workbook?
.. 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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Vlookup - Array not selecting in other workbook | Excel Discussion (Misc queries) | |||
vLookup with multiple lookup value in table array | Excel Worksheet Functions | |||
VLOOKUP keeping array lookup reference | Excel Discussion (Misc queries) | |||
VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY | Excel Worksheet Functions |