VLookups Across Mulitple Table Arrays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to do a vlookup using mulitple table arrays? I tried, and
couldn't get it to work, but maybe I wasn't doing it the right way...
 
Do you mean to look in one table and if it isn't there look in another
one, and so on? If so, here's a generic approach, but you'll need to
fill in the details yourself:

=IF(ISNA(vlookup(on_table1)),IF(ISNA(vlookup(on_table2)),"not
present",vlookup(on_table2)),vlookup(on_table1))

Hopefully, you can see the symmetry if you want to apply it to more
tables.

Hope this helps.

Pete
 
Well, what I'd like to do is check a table in 11 different sheets for a
specific account number and have a formula add all the amounts in a column
that relate to that account...
 
No, I would need to have the vlookup check about 11 different tables and add
up what it finds in all of the tables for the assigned value...any ideas?
 
What you want to do is doable but until you can give enough detail for
someone to help you, you won't get much help. No mind readers here. Send me
a workbook if you like but if you don't give me details I will just delete.
 
Sounds like you want to do a SUMIF across multiple sheets.

Try this:

Make a list of your sheet names in say, A1:A11.

This will sum B1:B10 if A1:A10 on any of the sheets listed contain the
account number XX:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A11&"'!A1:A10"),"XX",INDIRECT("'"&A1:A11&"'!B1:B10")))

If the account number is a numeric number like 9999 do not enclose it in
quotes. If the account number is an alpha-numeric like X99C then do enclose
it in quotes.
 
Back
Top