You are here:

Excel/question on your 3d vlookup and hyperlink.xls

Question
QUESTION: Jerry,

I am looking in particular at the first file listed for 3d vlookup and hyperlink.  In your example, you find a sheet name where a person's name appears using your array formula =INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets & "'!A1:A50"), A3),0))

I need to do almost the exact same thing, however I would have names appear in multiple sheets and need to return each of the sheet names where the name appears.  Can you help me figure out how to do that?  I would be most appreciative.

Thank you for your time!

ANSWER: That formula is completely unsuitable to do multiple lookups of data across multiple sheets.

From the limited info in your question I would expect a VBA program to be the only way to accompish that, probably a non-trivial one, too.

---------- FOLLOW-UP ----------

QUESTION: Thanks Jerry.  I will upload the example file to the site you indicated.  Basically, I will be using your same files, but adding names to multiple sheets.

Thanks again.

I would create columns and use the sheet names as the headers of those columns.  The search names in column A, the sheet names across B1:F1.

Then this formula in B2 copied down and across will "mark" each column (sheet) where the column A name can be found.

=IF(ISNUMBER(MATCH(\$A2, INDIRECT("'" & B\$1 & "'!A:A"), 0)), "x", "")

TO go the next step and return the matching HOURS instead of just marking an "x", the formula in B2 would be

=SUMIF(INDIRECT("'" & B\$1 & "'!A:A"), \$A2, INDIRECT("'" & B\$1 & "'!B:B"))

And lastly, to just search the sheets and SUM the hours from all the sheets, go back to your original "list" of sheets over in column G, then the B2 ARRAY formula would be:

=SUM(SUMIF(INDIRECT("'" & \$G\$1:\$G\$5 & "'!A:A"), \$A2, INDIRECT("'" & \$G\$1:\$G\$5 & "'!B:B")))

...confirmed with CTRL-SHIFT-ENTER.

Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thanks so much Jerry....that worked perfectly. Happy New Year!

• Ask a Question
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010