Reference MATCH() or INDIRECT() from another worksheet?

[criticalityevent]

Hi everyone!

In my example workbook (https://dl.dropboxusercontent.com/u/54467637/example1.xlsx), I have a hierarchy sheet (the real one is about 4600 rows) that I would like to reference. While troubleshooting my formula, I found that it only works if the hierarchy is in the same sheet (see “all in one” sheet).

I’ve tried variations on the formula in “Sheet1” to see if my references are off, but I just get different errors:

=VLOOKUP(MID(B2,3,2),INDIRECT(ADDRESS(MATCH(LEFT(B2,2),B:B,0),2,1,0,"hierarchy")):hierarchy!F25,5,FALSE) -results in #N/A

=VLOOKUP(MID(B3,3,2),INDIRECT(ADDRESS(MATCH(LEFT(B3,2),hierarchy!B:B,0),2)):hierarchy!F26,5,FALSE) -results in #VALUE

=VLOOKUP(MID(B4,3,2),INDIRECT(ADDRESS(MATCH(LEFT(B4,2),hierarchy!B:B,0),2,1,0,"hierarchy")):hierarchy!F27,5,FALSE) -results in #REF


As a side question, any ideas on how to fill the "specific product" column? I'm trying to attempt a nested MATCH() function with no luck, but I know I need it to match the first two columns before it can reference the correct specific product in the third column.

Thanks all!

 

[FDibbins]

cross-posted here...
Reference MATCH() or INDIRECT() from another worksheet?

 

[criticalityevent]

cross-posted here...
Reference MATCH() or INDIRECT() from another worksheet?

Yes, I do apologize for the cross-post, however, after multiple days of attempting to work through the issue myself and watching the "view" counts go up without so much as a suggestion, I don't know what else to do. You'll see how far back this issue has gone for me if you view the link posted in that thread linking back to my initial question on Monday. I've wasted days on this and am getting desparate.

 

[ukmikeb]

Hi

Is this what you're after :-

=VLOOKUP(MID(B4,3,2),INDIRECT(ADDRESS(MATCH(LEFT(B4,2),hierarchy!A:A,0),2,1,1,"hierarchy")):hierarchy!F27,5,FALSE)

I don't think you can mix R1C1 formulas with A1 type formulas in the same statement.

hth

 

[Joe4]

Cross-posting is permitted here, provided it is done properly. We just that you mention this is a Cross-Post and provide a link to the other thread so that others can see past replies (and possible future replies) on the topic so efforts are not unnecessarily duplicated. Reference rule #10 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[criticalityevent]

Hi

Is this what you're after :-

=VLOOKUP(MID(B4,3,2),INDIRECT(ADDRESS(MATCH(LEFT(B4,2),hierarchy!A:A,0),2,1,1,"hierarchy")):hierarchy!F27,5,FALSE)

I don't think you can mix R1C1 formulas with A1 type formulas in the same statement.

hth

Yes! Darn… I was so close with one of my attempts; I just had my lookup array pointing to the wrong column and that issue with the formula type. I had never even heard about formula types until I tried doing this. Thanks a ton!

Cross-posting is permitted here, provided it is done properly. We just that you mention this is a Cross-Post and provide a link to the other thread so that others can see past replies (and possible future replies) on the topic so efforts are not unnecessarily duplicated. Reference rule #10 here: Forum Rules

I knew I should’ve referenced my past post; I’ve been on forums long enough to where that should have been a given. I apologize for that. Should I create a separate thread for my other question regarding nested MATCH() functions in order to stay on a single topic, or should I just keep that in here?

 

[Joe4]

That's why you typically only want to post one question to a thread, even if it is the same project (unless it is a directly related follow-up question).
Go ahead and post your other question to a new thread.

 

[criticalityevent]

That's why you typically only want to post one question to a thread, even if it is the same project (unless it is a directly related follow-up question).
Go ahead and post your other question to a new thread.

Will do, thanks!