• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to lookup a value across multiple sheets and return sheet names [SOLVED]

P

Phil

New Member
I did a search on the forum and couldn't find a solution.


I have Employee Names across multiple sheets with each sheet representing a Division (sheet name).


I also have an input sheet where I have a list of all Employees. In the input sheet I want a quick way to identify which sheet/Division an employee is in by searching all of the other sheets and returning the sheet name where the Employee is located.


Thanks
 
Phil


Not something I've ever done, but there appears a good explanation here:

http://www.myonlinetraininghub.com/excel-vlookup-multiple-sheets
 
Thanks Hui but it's not exactly what I am after.


The result of the formula finds and returns a value from multiple sheets. I am after a formula that returns the name of the sheet where the value is found instead of the value itself.
 
Will be simple enough in VBA. Suggest you post a link to a sample file to make it easy for someone to whip up an example matching your specific data. See http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Phil ,


You can use the technique outlined in the link to get what you want.


If you have a list of all the sheet names in a named range called Sheet_List , then the following formula will tell you on which sheet the match occurs. If a match occurs in more than one sheet , only the first sheet name will be returned.


=INDEX(Sheet_List,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet_List&"'!$B$1:$B$400"),Employee_Name)>0),0))


where Employee_Name is a named range containing the name which is to be searched.


Narayan
 
jeffreyweir, thankyou for your feedback. I want to avoid VBA because no one else will understand it!
 
NARAYANK991 said:
Hi Phil ,


You can use the technique outlined in the link to get what you want.

NARAYANK991,
I have been attempting the formula you outlined with the referenced example from the link. I am unable to get it to work for me. I get the "The formula your typed contains an error." message when I attempt.

I am basically wanting to achieve the same results as Phil. I have a list on one sheet of PO's and I want it to search the other sheets for that PO and return which sheet that PO is located on. Thanks for any help!
If you have a list of all the sheet names in a named range called Sheet_List , then the following formula will tell you on which sheet the match occurs. If a match occurs in more than one sheet , only the first sheet name will be returned.


=INDEX(Sheet_List,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet_List&"'!$B$1:$B$400"),Employee_Name)>0),0))


where Employee_Name is a named range containing the name which is to be searched.


Narayan
Click to expand...
 
Hi ,

I hope you have changed the formula to the following :
Code: 
=INDEX(Sheet_List,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet_List&"'!$B$1:$B$400"),Employee_Name)>0),0))
Narayan
 
I tried the formula you just gave and it returns with #N/A for the first 11 cells it is copied into then #VALUE! for the rest of the cells in that column. My formula looks like: =INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$2:$B$1000"),ProjectNumbers)>0),0)) I have SHEETLIST as a highlighted range with the names of the sheets and ProjectNumbers as the highlighted range with the project numbers. I changed to $B$2:$B$B1000 because that is where my project numbers are on the various sheets that I'm trying to match to my highlighted ProjectNumbers. Thanks!
 
Hi ,

The INDIRECT has to encompass the entire address , which is composed of the beginning "'" single-quote character , the sheet name , the closing "'" single-quote character , the "!" character followed by the range address within quotes.

Is your ProjectNumbers just a way of specifying the address or is it a named range ? If it is the latter , it already contains the sheet name unless you have defined it correctly. Can you confirm ?

Narayan
 
Narayan,
I have a column E with 500 different project numbers,that I highlighted and called "ProjectNumbers", This is all on the sheet titled "Master". Then I have 12 sheets that each have a column B with varying amounts of project numbers. I have a list of the names of those sheets on a separate sheet titled "Sheets". I highlighted that list of names and called it "SheetList" I want to be able to have a column C on my "Master" sheet tell me which sheet the project number it is looking up is found on. Hope this clarifies.
 
Hi ,

I think your problem is a different one ; the original formula had a list of all employees in a separate sheet , and the formula referred to each employee one by one ; for each employee , the formula looked up all the sheets to return the corresponding amount from which ever sheet had a match.

In your case , you are using ProjectNumbers to refer to the entire list of Project Numbers ; what you should be doing is , if your list of project numbers is in the range E2 through E501 , then in say F2 , you would have the formula :

=INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$2:$B$1000"),E2)>0),0))

which would return the sheet name where a match was found for the project number in cell E2 ; when you copy the above formula downwards , each cell in column F would contain the corresponding sheet name.

Narayan
 
NARAYANK991 said:
Hi ,

I think your problem is a different one ; the original formula had a list of all employees in a separate sheet , and the formula referred to each employee one by one ; for each employee , the formula looked up all the sheets to return the corresponding amount from which ever sheet had a match.

In your case , you are using ProjectNumbers to refer to the entire list of Project Numbers ; what you should be doing is , if your list of project numbers is in the range E2 through E501 , then in say F2 , you would have the formula :

=INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$2:$B$1000"),E2)>0),0))

which would return the sheet name where a match was found for the project number in cell E2 ; when you copy the above formula downwards , each cell in column F would contain the corresponding sheet name.

Narayan
Click to expand...


Hi, the formula worked for me however, how can I have the formula return the last sheet my value is located in if it's located in multiple sheets? Let's say using this example, E2 shows up in SheetList three times. I want it to return the sheet name is was most currently updated in,

Thanks,
 
Hi Narayan,

This was very helpful to a similar problem I had...I have one question though...why is the "--" needed in the match function? If I delete that it does not work, I noticed I can use "----" and that would work as well, so it seems like it is just a minus sign twice (so a positive sign, but a + sign will not work)...could you enlighten me?

Thanks!

Tony
 
@Tony
-- is known as a Double Unary
It is like saying -1 x -1 x what ever is next
which is the same as saying 1 x what ever is next

It is used to force logic arrays of True/False values to convert to arrays of 1/0's
the double unary is exactly equivalent of 1x or 0+ etc, it is the facility of doing any math on the array of True/False that forces Excel to convert them to 1/0's
The Double Unary -- is the most efficient way for Excel to force this

You can read about them in the section called "Double Unary" at
http://chandoo.org/wp/2011/12/21/formula-forensics-no-007/
 
@Tony

Also be careful, You must use -- not - or --- as you suggested

If I have a range of A1:A4 = 2,1,2,1

A simple formula =(A1:A4=2) will form an array of {TRUE;FALSE;TRUE;FALSE}

The formula =--(A1:A4=2) will form an array of {1;0;1;0} as it should
with 1 corresponding with True and 0 with False

Using either formula =-(A1:A4=2) or =- - -(A1:A4=2) will form an array of {-1;0;-1;0} Which maybe isn't exactly what your were trying to achieve

To test this enter the values I described above
Then in a cell enter =--(A1:A4=2) now press F9
Excel will display
upload_2015-9-24_8-31-44.png

Try it again with - or - - -

You will get:
upload_2015-9-24_8-32-28.png
 
Thank you so much for your second posting, that's exactly what I wanted to understand. The first one only confirmed what I thought, but the second one made it clear. Yes, not that I didn't say "---" (which as you pointed out would be -1) what I said was "----" (that is 4 negative signs, which then would get you back to 1, I did that just to test that indeed all it was doing was multiplying -1 x -1 (so it should work if I do it 4, 6 or any even number of times).

Why would a "+" sign instead of "--" not work the same way though? (If all we are trying to do is to convert from boolean to integer shouldn't it work the same?)... it reminded me of the old Lotus123 when you started formulas with the + sign (instead of the = sign in Excel).

Anyway, thank you so very much for explaining it so clearly. It was great learning for me.

Tony
 
Tony
Performaing any math on an array of Logicals forces Excel to convert the array to an array of 1/0's before applying the maths

So - - (Array) is the same as 0+(Array) or 1*(array) or 20*(Array)/20 or (Array)/1

- - (Array) is the fastest way to achieve it and so is generally recommended
I find 1*(Array) easier to read

Where you have multiple logicals or arrays you can remove this and use the arrays against each other

eg:
=Sumproduct(- - (A1:A10=20), B1:B10)
can be simplified to:

=Sumproduct( (A1:A10=20)*( B1:B10))

The math of the * the second range forces Excel to convert the Logical Array (A1:A10=20) to an array of 1/0's before the Multiply takes place
 
Hi @NARAYANK991 or others

I understand this is an old post, but as opposed to asking a new question, I hope this can be appended.

Can this formula be altered to return ALL names of sheets that the value exists in?

Actually, maybe what I need is a way to re-write the formula to return the 2nd sheet the value/name appears in, the 3rd sheet (as opposed to the first sheet in which the value appears)... into separate column cells. Then I can combine those as needed.
 
Last edited:
LAP said:
Hi @NARAYANK991 or others

I understand this is an old post, but as opposed to asking a new question, I hope this can be appended.

Can this formula be altered to return ALL names of sheets that the value exists in?

Actually, maybe what I need is a way to re-write the formula to return the 2nd sheet the value/name appears in, the 3rd sheet (as opposed to the first sheet in which the value appears)... into separate column cells. Then I can combine those as needed.
Click to expand...
---In this thread: http://forum.chandoo.org/threads/ho...ts-and-return-addition-all-sheet-names.29525/
my question was solved. The formula returns each sheet name a value exists in, in individual cells.
 
You must log in or register to reply here.
Back
Top