Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Hi Jay,

Could Data Filter AdvancedFilter be of any help ???


HTH
Cheers
Carim

If I've understood the problem correctly, and that you need to find
one record, for each of one of 30 lookup values, in several tables,
then conceptuallly one approach would be a macro which would

a) loop down the list of 30 lookup values selecting each value in
turn.
b) loop through each of your tables and perform a lookup on each table
in turn using the selected value as the input. As soon as a record is
found, copy the record and record it somewhere, then exit the sub
c) if not found move on to the next table etc.

If your table names have some consistency, like IncomeTax1, IncomeTax2
as your post suggests, then the loop counter - 1,2,3 etc could be
incorporated in macro definition of the lookup table.

Hope this suggestion helps.




On Thu, 31 Aug 2006 01:37:02 -0700, Jay
wrote:

Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Carim:

I don't know if it will? How will it solve my problem, give examples? The
target record (named ranged) records go from 1 - 500,000, however, excel has
a record limit of 65,000 records, thus, I'm using multiple named rages (of
+/- 65,000 record on one sheet) as mentioned earlier, hopefully to get around
the 65,000 record limit of excel. So, I'll to find a value from within any
one of those named range records to match any one of those on the first sheet
comprised of 30 records. Hope this helps explain the problem I'm facing....

Thanks again,

"Carim" wrote:

Hi Jay,

Could Data Filter AdvancedFilter be of any help ???


HTH
Cheers
Carim

Jay,

Would you consider using VBA?

Below is a sample routine which looks through tables defined by the "Table"
array. It returns -999 if no match found

To use:

=MyLookup(S5)

Function MyLookup(myVal)
Table = Array("Table01", "Table02")
For i = 0 To Ubound(Table)
res = Application.VLookup(myVal, Range(Table(i)), 2, 0)
If Not (IsError(res)) Then
MyLookup = res
Exit Function
End If
Next i
Mylookup=-999
End Function

"Jay" wrote:

Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Try the formula - if you do not get a match in any of the 3 tables (in
my example) you will get the message "Not present", so you can change
this part to "" if you want the cell to appear blank in this
circumstance.

Hope this helps.

Pete

Jay wrote:
Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Pete's solution handles the N/A error with his "Not Present" message. If you
need to check for a genuine zero value you will need additional IF statements
and you then exceed the Excel limit of 7 IFs. You can get round this by
combining several IF statements but it becomes a complex statement.

In Pete's formula you will need to replace (I think!)

"not present", VLOOKUP(S5,table_3,2,0) with

"not present", IF(VLOOKUP(S5,table_3,2,0)=0,"",VLOOKUP(S5,table_3 ,2,0) etc

You can get around the 7 nested If statements limitation a few different
ways. This web page gives direction on how to do it.

http://www.cpearson.com/excel/nested.htm


In my previous posting, I offered a VBA solution using a simply function
which searches the tables.


"Jay" wrote:

Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Hi Toppers,

Jay's question related to his original 30 records being 0 or blank,
which he thought would return #N/A from the VLOOKUPs, so the ISNA part
of my formula takes care of this, as you say.

If there are blanks in the lookup tables themselves, then these will
return 0 as a value, and if Jay wants these to appear blank then I find
the easiest way is to set up conditional formatting to use a white
foreground colour if the cell is zero - easier than amending the
formula I gave him (which may well stretch to have 5 lookup tables at
least).

Pete

Toppers wrote:
Pete's solution handles the N/A error with his "Not Present" message. If you
need to check for a genuine zero value you will need additional IF statements
and you then exceed the Excel limit of 7 IFs. You can get round this by
combining several IF statements but it becomes a complex statement.

In Pete's formula you will need to replace (I think!)

"not present", VLOOKUP(S5,table_3,2,0) with

"not present", IF(VLOOKUP(S5,table_3,2,0)=0,"",VLOOKUP(S5,table_3 ,2,0) etc

You can get around the 7 nested If statements limitation a few different
ways. This web page gives direction on how to do it.

http://www.cpearson.com/excel/nested.htm


In my previous posting, I offered a VBA solution using a simply function
which searches the tables.


"Jay" wrote:

Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

And if Jay wants a blank returned if S5 contains a blank, but otherwise
wants the matching value or some message like "not present", then the
formula I gave him can be amended as follows:

=IF(OR(S5=0,S5=""),"",IF(ISNA(VLOOKUP(S5,table_1
etc_plus_rest_of_my_formula_with ) at the end

Hope this helps, Jay.

Pete

Pete_UK wrote:
Hi Toppers,

Jay's question related to his original 30 records being 0 or blank,
which he thought would return #N/A from the VLOOKUPs, so the ISNA part
of my formula takes care of this, as you say.

If there are blanks in the lookup tables themselves, then these will
return 0 as a value, and if Jay wants these to appear blank then I find
the easiest way is to set up conditional formatting to use a white
foreground colour if the cell is zero - easier than amending the
formula I gave him (which may well stretch to have 5 lookup tables at
least).

Pete

Toppers wrote:
Pete's solution handles the N/A error with his "Not Present" message. If you
need to check for a genuine zero value you will need additional IF statements
and you then exceed the Excel limit of 7 IFs. You can get round this by
combining several IF statements but it becomes a complex statement.

In Pete's formula you will need to replace (I think!)

"not present", VLOOKUP(S5,table_3,2,0) with

"not present", IF(VLOOKUP(S5,table_3,2,0)=0,"",VLOOKUP(S5,table_3 ,2,0) etc

You can get around the 7 nested If statements limitation a few different
ways. This web page gives direction on how to do it.

http://www.cpearson.com/excel/nested.htm


In my previous posting, I offered a VBA solution using a simply function
which searches the tables.


"Jay" wrote:

Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

To Pete_UK and Toppers:

Thanks guys, you both are the best! I appreciate ALL the assistance!

Jay

"Pete_UK" wrote:

Hi Toppers,

Jay's question related to his original 30 records being 0 or blank,
which he thought would return #N/A from the VLOOKUPs, so the ISNA part
of my formula takes care of this, as you say.

If there are blanks in the lookup tables themselves, then these will
return 0 as a value, and if Jay wants these to appear blank then I find
the easiest way is to set up conditional formatting to use a white
foreground colour if the cell is zero - easier than amending the
formula I gave him (which may well stretch to have 5 lookup tables at
least).

Pete

Toppers wrote:
Pete's solution handles the N/A error with his "Not Present" message. If you
need to check for a genuine zero value you will need additional IF statements
and you then exceed the Excel limit of 7 IFs. You can get round this by
combining several IF statements but it becomes a complex statement.

In Pete's formula you will need to replace (I think!)

"not present", VLOOKUP(S5,table_3,2,0) with

"not present", IF(VLOOKUP(S5,table_3,2,0)=0,"",VLOOKUP(S5,table_3 ,2,0) etc

You can get around the 7 nested If statements limitation a few different
ways. This web page gives direction on how to do it.

http://www.cpearson.com/excel/nested.htm


In my previous posting, I offered a VBA solution using a simply function
which searches the tables.


"Jay" wrote:

Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Pete,
Apologies ... I must read the responses more carefully.

"Pete_UK" wrote:

Hi Toppers,

Jay's question related to his original 30 records being 0 or blank,
which he thought would return #N/A from the VLOOKUPs, so the ISNA part
of my formula takes care of this, as you say.

If there are blanks in the lookup tables themselves, then these will
return 0 as a value, and if Jay wants these to appear blank then I find
the easiest way is to set up conditional formatting to use a white
foreground colour if the cell is zero - easier than amending the
formula I gave him (which may well stretch to have 5 lookup tables at
least).

Pete

Toppers wrote:
Pete's solution handles the N/A error with his "Not Present" message. If you
need to check for a genuine zero value you will need additional IF statements
and you then exceed the Excel limit of 7 IFs. You can get round this by
combining several IF statements but it becomes a complex statement.

In Pete's formula you will need to replace (I think!)

"not present", VLOOKUP(S5,table_3,2,0) with

"not present", IF(VLOOKUP(S5,table_3,2,0)=0,"",VLOOKUP(S5,table_3 ,2,0) etc

You can get around the 7 nested If statements limitation a few different
ways. This web page gives direction on how to do it.

http://www.cpearson.com/excel/nested.htm


In my previous posting, I offered a VBA solution using a simply function
which searches the tables.


"Jay" wrote:

Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,

Well, we are all guilty of that at times !! <g

Pete

Toppers wrote:
Pete,
Apologies ... I must read the responses more carefully.

"Pete_UK" wrote:

Hi Toppers,

Jay's question related to his original 30 records being 0 or blank,
which he thought would return #N/A from the VLOOKUPs, so the ISNA part
of my formula takes care of this, as you say.

If there are blanks in the lookup tables themselves, then these will
return 0 as a value, and if Jay wants these to appear blank then I find
the easiest way is to set up conditional formatting to use a white
foreground colour if the cell is zero - easier than amending the
formula I gave him (which may well stretch to have 5 lookup tables at
least).

Pete

Toppers wrote:
Pete's solution handles the N/A error with his "Not Present" message. If you
need to check for a genuine zero value you will need additional IF statements
and you then exceed the Excel limit of 7 IFs. You can get round this by
combining several IF statements but it becomes a complex statement.

In Pete's formula you will need to replace (I think!)

"not present", VLOOKUP(S5,table_3,2,0) with

"not present", IF(VLOOKUP(S5,table_3,2,0)=0,"",VLOOKUP(S5,table_3 ,2,0) etc

You can get around the 7 nested If statements limitation a few different
ways. This web page gives direction on how to do it.

http://www.cpearson.com/excel/nested.htm


In my previous posting, I offered a VBA solution using a simply function
which searches the tables.


"Jay" wrote:

Pete_UK:

Thanks, that's what I was looking for, however, if any of the original 30
records are blank ("0" or "") records, I'd like for the results column to not
show "0" or N/A or #value! error messages. How can I get that with your
combined VLOOKUP function?

Looking forward to your reply!

Thanks muchly!

"Pete_UK" wrote:

As I understand it, you have 300,000 reference records - you want to
consider this as one contiguous table.

Because of Excel's limit of 65k rows, you will have to split this table
up into at least 5 sub-tables - assume you give these names like
table_1, table_2, table_3 etc., then you can combine multiple lookups
as follows:

=IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t
present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0))

Basically, if it exists in table_1 then get the corresponding data,
otherwise if it is in table_2 get the data from there, otherwise try
table_3 and if not there then return the error message. Hopefully you
can see how this could be extended to more tables.

Hope this helps.

Pete

Jay wrote:
Hey Guy & Gals:

To All the Excel guru's out there....

I have this Excel application which requires a combining of named ranges to
accomplish what I need in values. What I have is for example 30 records on
one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000
records to search against for returned values which may match the data
against the 30 records of the first sheet. The VLOOKUP function I currently
have looks like so;
=IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax
Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need
to extend my VLOOKUP range searches to include various named ranges, since
Excel only handles 65,000 records vertically. How can I pull it all together
an have the VLOOKUP scan multiple ranges for values which may match those in
the first 30 records as mentioned earlier?

In the above example, I point to the worksheet named "Tax Table", however,
there is also a named range called "IncomeTax", the other is "IncomeTax1" and
"IncomeTax2 etc,etc,etc on the same sheet. How can this be done?

Thanks,