Return Lookup value online if column matches criteria?

Hello and welcome to the forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by kev_

I would create the normal Vlookup formulas, but that can be avoided. You can probably use the INDIRECT function to do what you want.
The principle is that you build a string to create the reference you want and wrap INDIRECT around the string, and place that in the lookup formula.
If you need assistance, attach a workbook, with a few examples demonstrating what you want.
To upload - click GoAdvanced\ look below and click on ManageAttachments, follow screen instructions.
Thanks

Thank you. I have edited my original post with an attachment.

You only included 2 tabs (worksheets) in your sample.

Are you saying that you will have multiple Dept tabs (e.g. 'Dept - 01', 'Dept - 02',...) or multiple months tabs (e.g. 'Jan', 'Feb', ...)?

If you want a single Dept tab and multiple months tabs, is the goal to change cell A1 of the 'Dept - 01' worksheet to "Dept - 02", for example, and have the formula return the corresponding column of the Jan worksheet? If so, shouldn't the 'Dept - 01' worksheet be re-named since it will not always be for Dept - 01?

Maybe it's okay

Formula:

Please Login or Register  to view this content.

Originally Posted by 63falcondude

Are you saying that you will have multiple Dept tabs (e.g. 'Dept - 01', 'Dept - 02',...) or multiple months tabs (e.g. 'Jan', 'Feb', ...)?

If you want a single Dept tab and multiple months tabs, is the goal to change cell A1 of the 'Dept - 01' worksheet to "Dept - 02", for example, and have the formula return the corresponding column of the Jan worksheet? If so, shouldn't the 'Dept - 01' worksheet be re-named since it will not always be for Dept - 01?

Both. There are ~50 Dept tabs. Each containing 12 months. You can ignore the months for now, because I can reference a different tab for each month. What I want to do is do all of the formulas for each of these columns, and then copy it into the other tabs. The problem is I will have to change the column reference manually for each tab. What I want is for Excel to look at the Dept # cell in each tab and only return data for that column. My data tab (Jan, Feb, Mar, etc) will have multiple value rows, and each Dept is different. I want to return the relevant data for the row based on the dept #.

Originally Posted by Glenn Kennedy

In B3, copied across and down:

=IFERROR(INDEX(INDIRECT("'"&B$2&"'!B2:P10"),MATCH($A3,INDIRECT("'"&B$2&"'!A2:A10"),0),MATCH($A$1,INDIRECT("'"&B$2&"'!B1:P1"),0)),"")

This works beautifully. Can you explain how this works? This does even MORE than I needed it to. I thought I would have to manually point each column to the correct month tab but this not only looks for the Dept #, it also does the month for me.

Originally Posted by Glenn Kennedy

It is fundamentally an INDEX-MATCH-MATCH formula.

The INDEX is the 2D array in which the answer is to be found: the first MATCH returns the row number corresponding to the Value and the second MATCH the column number corresponding to the Department.

Superimposed into that are a number of INDIRECT functions, which lift the sheet name from row 2 and the Departmnet name from A1. It means that you don't need one sheet fpr every Dept. You can have a nice drop-down in A1 which will enable you to select any department with click of your moose and see the whole year at a glance.

Do you know how to make a dropdown list for the departments?

If so, then you're welcome. If not, then ask.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

I don't think the drop down will work for me. This data ultimately drives another worksheet that needs references to certain department tabs.

Thanks!

Originally Posted by Glenn Kennedy

OK. You're welcome anyhow.

OK, once I adapted your formula into my real spreadsheet it doesn't seem to be working. If you don't mind, please look at the updated sample attached. I have moved somethings to make it more relevant to my real spreadsheet. It doesn't appear to be matching the VALUE in column A correctly.

Thanks,

You have to adjust the ranges to fit your layout.

Using Glenn's formula:

B9 =IFERROR(INDEX(INDIRECT("'"&B$8&"'!B7:P19"),MATCH($A9,INDIRECT("'"&B$8&"'!A7:A19"),0),MATCH($A$4,INDIRECT("'"&B$8&"'!B6:P6"),0)),"")