VLookup Table Updates

[Carrie]

Hi,

I have created a formula that looks up some data I need.
The formula is as follows:

=IF(ISERROR(VLOOKUP(+A11,EEFData_XLSReport'!
$A$989:$I$1094,8,FALSE)),0,VLOOKUP(+A11,EEFData_XLSReport'!
$A$989:$I$1094,8,FALSE))

This formula works fine; however, the information I'm
pulling from the table to another spreadsheet is sorted by
year and 3 subcategories in each year. This means that
each time I need to update the tables to the current
month's information, I have to go to each individual
formula and tell it what section of data to use as its
table. This takes me about 30 minutes. I would like to
be able to have my formula automatically narrow the
table's range based on the year and subcategory . Does
anybody know of a way to do this?

Thanks,

Carrie

 

[Harlan Grove]

I have created a formula that looks up some data I need.
The formula is as follows:

=IF(ISERROR(VLOOKUP(+A11,EEFData_XLSReport'!
$A$989:$I$1094,8,FALSE)),0,VLOOKUP(+A11,EEFData_XLSReport'!
$A$989:$I$1094,8,FALSE))

This formula generates syntax errors due to the unpaired single quotes. How
about showing the ACTUAL formula IN FULL?

This formula works fine; however, the information I'm
pulling from the table to another spreadsheet is sorted by

By 'another spreadsheet' do you mean a separate Excel .xls file?

year and 3 subcategories in each year. This means that
each time I need to update the tables to the current
month's information, I have to go to each individual
formula and tell it what section of data to use as its
table. This takes me about 30 minutes. I would like to
be able to have my formula automatically narrow the
table's range based on the year and subcategory . Does
anybody know of a way to do this?

If, for example, year were in column D of your data range, which I'll assume is
in full '<whatever>EEFData_XLSReport'!$A$1:$I$12345, and you needed to run the
VLOOKUP only for year 2003, then you could try

=IF(SUMPRODUCT((INDEX('<whatever>EEFData_XLSReport'!$A$1:$I$12345,0,4)=2003)
*(INDEX('<whatever>EEFData_XLSReport'!$A$1:$I$12345,0,1)=A11)),
INDEX('<whatever>EEFData_XLSReport'!$A$1:$I$12345,0,8),
MATCH(1,(INDEX('<whatever>EEFData_XLSReport'!$A$1:$I$12345,0,4)=2003)
*(INDEX('<whatever>EEFData_XLSReport'!$A$1:$I$12345,0,1)=A11),0)),0)