Lookup formula help in excel

Water Cooler
#1

I have 2 excel workbooks

workbook A = list of clients by account #

workbook B = client revenue but there are 30 tabs and the client can be present in any of these 30 tabs

task: Is there a formula I can run in workbook A that can help me locate what tab in workbook B the client is in? I need the name of the tab.

#2

You either need to hardcode the tabs into the formula, or use indirect to make ti where you create a list of tab names and the formula uses that to populate it. I’m not sure of any easy way to use a formula to look across sheets without VBA. You’d need to insert a formula that givevs you the worksheet name in the same spot in each worksheet to make this easy

This looks like the way I’d do it:

http://ccm.net/faq/6215-lookup-across-whole-workbook-multiple-sheets

#3

I don’t know if there is a built-in way to do this.

I suppose you could make a big dynamic array of all the Workbook B fields, and then perform a lookup on this array. You would have to manually construct the array once, though.

Otherwise, you could use a macro or custom function, where you loop a lookup across workbook names defined in some list.

#4

If you can add the name within the same cell of each tab im sure you could use a sumif across the tabs that pulls a binary reference (if equal then 1, if not =0) that will allow you to reference the tab’s name.

Probably a lot of work but if you’re reusing this file then probably not a waste of time.

#5

  1. This is what SQL is for

  2. I think you could probably do this in Excel with Indirects or VBA. The performance might suck though

  3. I think on another Excel thread comp_sci_guy mentioned some other programming method to do something similar. I wasn’t familiar with it.

#6

^Could also try to import it into MS Access as well if not familiar with SQL