Excel 2007 : Multiple Vlookups

I created a name for the info in each sheet using "Name Manager" and input the name in the formula. This works if I'm referencing one sheet but not multiple. Not sure if I answered your questions.

If it helps, I can attach the file so you can get an idea of what I would like to do?

Ok...how do I attach a file to this thread?

Please find the attachment for my "test" file. Thank You.

OMG!!! Thank you so much! How did you consolidate all the names in name manager? Trying to figure out exactly how you did, what you did.

Originally Posted by NBVC

See attached.

First, create a list of sheetnames somewhere. I chose M2:M4 of the Search sheet. Name this range. I chose to name it Sheets.

Then apply formula in B5:

Please Login or Register  to view this content.

confirm this with CTRL+SHIFT+ENTER not just ENTER and then copy down and across.

To hide the 0's, format the range as Custom: 0;-0;;@

Ok..created the sheet names on my search page (as you did) and input the range for each name. I created a name to incorporate all sheets in the name manager. I copied and pasted this formula into the cell. I changed the reference from "sheets" to my name and the formula is not working. What am I doing wrong?

Originally Posted by NBVC

Did you confirm the formula with CTRL+SHIFT+ENTER instead of just ENTER before copying down and/or across?

I apologize NBVC for being so dense but I copied your formula from this forum. I then clicked on the cell in my workbook and paste the formula. I tested the formula by typing something in the search cell (to the left of the actual formula) and nothing was returned. When I click on the cell with the formula and click on fx next to the formula bar, it returns "IFERROR--value=volatile; value_if_error="" ". I didn't copy anything down or across yet. Just checked the one cell.

Originally Posted by NBVC

Did you confirm the formula with CTRL+SHIFT+ENTER instead of just ENTER before copying down and/or across?

Is this a code that I should place in a macro?

Originally Posted by NBVC

After you enter the formula in the cell and the cell is still active (i.e. a cursor still in it), then just hold the CTRL and SHIFT keys down, and press ENTER. You should see curly { } brackets surround the formula. Then it should give you your expected results... then just copy down and across.

Thanks so much. It works but I didn't know that the name of the cells (M4:M6) had to be exactly the same as the sheet tabs. That was one of the other issues. I really appreciate all your effort to help. Thanks again.