VLOOKUP to return multiple corresponding values across multiple sheets

[amarkbass]

In a previous post someone referenced this technique as a fix:


http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx


what if, you needed to search one source and return values accross multiple tabs/sheets?

ie: one tab for Ashsish, another for Sanjay and so on?

The row function that uses a colon ROW(1:1) will not work when referencing another tab... or will it?

 

[Montrey]

Set up some named ranges for the different sheets and bam!

 

[amarkbass]

by named ranges, you're referring to the name manager in the formula ribbon right?

 

[amarkbass]

or instead of the row(1:1) thing, use named ranges?

 

[amarkbass]

problem solved! was able to pop the sheet name in there...


before: ROW(1:1)

after: ROW('Sheet Name'!1:1)