Possible VLOOKUP Function? Trying to search through multiple sheets

[Mattchuex]

Hello All,
I am new here, I am also somewhat new to excel and completely self taught. I have a distant background with programming and coding. That being said I have restructured how my agency collects data on work projects. I understand what I am capturing but want to structure my work even easier to the keyboard peckers I work with. I am hoping so see if the is a function that would allow me to target many data points from a specific Sheet. A normal Project Work book might contain up to 20 Project Numbers. In the Summery section, I would like to allow the those less familiar with computers/ Excel to simply input the Project number (i.e. 089, 028,253) and have all the totals summerize. I would believe this to be some type of VLOOKUP but not sure.
In my example included using the "A2" to trigger the user to autofill "A2 thru P2" etcetera. I would like to avoid VB, its been 20 years since I have played in VB or C++.

Thank you in adavnce.
Matt

[PeteABC123]

Welcome to the forum Mattchuex.

Looks like the summary tab is built to display the desired result.

We need to see how your source data is laid out though. Can you update with a small representative example? It does appear that a simple vlookup would do it though.

Pete

[thenewkidd]

Yes there is a way to search specific data points through many functions
Vlookup, Index/Match, etc.

But the workbook you posted doesn't really tell me what you mean by "all the totals".

Can you give an example of what you are looking for in an "end result"? with the data set?

[Mattchuex]

Thanks for the response. The source data is displayed just like the summery... But I'll send an example

[thenewkidd]

What you are looking for is "Indirect" referencing to the table you want to look up.

This only works if the headers match every column of the table.
The table has to be the same name as the project number

And it is looking for a Row named "Totals" on the searched table.

INDEX(INDIRECT("'"&$A6&"'!$A$1:$Q$100"),MATCH("TOTALS",INDIRECT("'"&$A6&"'!$A:$A"),0),MATCH(B$1,INDIRECT("'"&$A6&"'!$2:$2"),0))


If there is a space in the wrong spot on one of the tables that will return an error.

At any rate.

Here is what you are looking for.

Edit to add, Forgot to change the Match variables to the "indirect" formula used in index.
Added the Match Variables to the Indirect formula.

[Mattchuex]

You helped this guy out huge! for my general knowledge, In your function it targets '011' but will pull up any sheet name/number, how does that work? Thank you so much from my self and my fire agency.
INDEX(INDIRECT("'"&$A8&"'!$A$1:$Q$100"),MATCH("TOTALS",'011'[/B]!$A:$A,0),MATCH(B$1,'011'[/B]!$2:$2,0))

[thenewkidd]

I updated the Formula in my original response.
I didn't mean to put '011' in the MATCH part of the formula.
But the reason why it works is because the table headers match exactly between all tables between all tabs. If the tables were different on each tab it wouldn't work.

INDEX(INDIRECT("'"&$A6&"'!$A$1:$Q$100"),MATCH("TOTALS",INDIRECT("'"&$A6&"'!$A:$A"),0),MATCH(B$1,INDIRECT("'"&$A6&"'!$2:$2"),0))


To expand in Column A
You can have a list of Project Numbers.

If the Project Number matches the Tab name it will look up the Table on that Tab.

Call it a typo , I usually "code" in bite-sized chunks then expand out

[Mattchuex]

Ok, I thought I had it pretty well understood in reality, not so much. I tried plugging the function into my actual working project data book and I get nowhere.
My totals row is; C127, D127, E127, etc, etc. I will include a little more summary with what I visual look at everyday.
My working actual sheets have red tabs.
Thank you so much...thenewkidd

[thenewkidd]

You changed the total set up of what '011' looks like vs '089A'

The lookup row is 2:2 in the first 2 tabs but the last tab is 5:5
All headers in Row 5 of '089A' have to match ALL tabs with a name have to match character by character on the Summary Tab

The formula is much more complex if your headers vary from row to row.

The long and short of it is All tabs should have the same exact format for ease of calculating. Last where the formula reads $2:$2 in my last "Match" replace the 2s with 5s and it should work but will break the first two tabs because of the row headers it is looking up.

You have two choices.. Move the header names in Row 2 of all tabs or change all the headers in all tabs in row 5 and change the formula to read
INDEX(INDIRECT("'"&$A6&"'!$A$1:$Q$100"),MATCH("TOTALS",INDIRECT("'"&$A6&"'!$A:$A"),0),MATCH(B$1,INDIRECT("'"&$A6&"'!$5:$5"),0))

Also, it is best not to deal with Merged Cells.

[thenewkidd]

To help understand how the formula is working I am going to break it down
(My intent is not to sound condescending, I just believe understanding a forula gives you the power to use it for other applications other than copy / paste blindly)


The Index/Match lookup is a combo type formula that uses columns and rows to narrow down a specific cell to reference. It returns number values.

For example MATCH("TOTALS",A:A,0) is counting down how many rows till it gets to a "match" to the word "Totals". If it is down 10 rows, the number will return 10. down 8 rows, it returns 8. etc.

You can do this to MATCH(A1,2:2,0) meaning what ever is in A1 will search the entirety of row 2 and count the number of columns until it gets to a match of what is in A1.

Index is basically the table you are searching. Setting up a table from A1:Q100 then needs a number indicating how many rows down you want to count and then how many columns across to count broken up by commas.

The Indirect formula cannot be "ranged" unless referencing a named range. it is generally only a single target formula unless you use array (different animal, walk before you run).
Indirect reads a named range or it reads a cell that you want named.

For example: if you put something in Cell A1 (Call it John)... and in Cell C5 you type the cell name "A1" then in C4 you put INDIRECT(C5) It will return "John" because it is reading the cell name "A1" and what is in that cell.

So combining this and I know it is a lot to take in... we are referencing table names and table rows and table columns.



TL;DR Version:
1. There is no consistency with your workbook tabs.
2. your headers on your summary page do not match up to data that is able to be looked up on your project name tabs.
3. Merged Cells make it very difficult to make sure a cell is being referenced in the correct spot. (this can be done, but it makes it harder).
4. I fixed the formula to fit in the "Project Summary" but it won't be perfect until all tabs are consistent and best if they do not have merged cells.

lastly
The reason why you could not look up '011' in the original summary tab is because excel was reading 011 as just 11 (with no zero), So it was trying to look up the numeric value 11 instead of the text value of 011.

This was a fun experiment but full errors if Headers contain misspellings and extra spaces where there should be none.

Hope this helps, I will check back on this thread periodically if you need extra help.

INDEX(INDIRECT("'"&$A6&"'!$A:$AG"),MATCH("TOTALS",INDIRECT("'"&$A6&"'!$A:$A"),0),MATCH(B$5,INDIRECT("'"&$A6&"'!$5:$5"),0))