Vlookup accross many tabs

**festering** · 10-27-2009, 11:20 AM

Hi,

i need to be able to alter the tab which is specified in a VLOOKUP command depending upon which value is given in a drop down box.

ie if the drop down box reads 2 the VLOOKUP should search for an item reference given on tab 2 etc.

Does this make sense?

**ron2k_1** · 10-27-2009, 11:40 AM

Originally Posted by festering

Hi,

i need to be able to alter the tab which is specified in a VLOOKUP command depending upon which value is given in a drop down box.

ie if the drop down box reads 2 the VLOOKUP should search for an item reference given on tab 2 etc.

Does this make sense?

This has been one my "ever-to-puzzle-me" questions. I'm pretty sure that other can give you a more elaborate function, or even a code (which means that you are on the wrong thread), but the way I have done it is this:

If I have multiple tabs that have similar data (that is all the data is organized in similar number of columns, and have a common lookup value (column 1). I would name the range on each tab (depending on what it is, for instance if it is years, I would name them "2008", "2007", "2006", etc.) You do this by Highlighting the range and going to Insert>Name>Define.

Then on the sheet where I would put the function I would use a nested IF function, i.e, IF vlookup criteria says Year 2008, then to look in 2008 range, if it is 2007 then to look in the 2007 name range, etc. [Note in the table array argument of the vlookup function insert the name of your range]

Hope this helps

Ron

**festering** · 10-27-2009, 11:58 AM

It is a good suggestion, however i have found another way of doing it by using the IFERROR function to systematically search a sequance of tabs, however i have now come accross another problem i have too many tabs and therefore to many arguments. do you know how to overcome this problem?