You are here:

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

Advertisement


Question
QUESTION: Jerry,

I found the help you  have posted on your link at https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/files.  

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.

Answer
Match Multiple Sheets
Match Multiple Sheets  

Sum Multiple Sheets
Sum Multiple Sheets  
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.

About Excel
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


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

©2016 About.com. All rights reserved.