Reference another Excel sheet using a cells value

Question

My fx attempt:

=IF(MATCH(B1,Sheet1!$AY$2:$AY$50000,0) > 0,Sheet1! <problem is here> ,"")

so i need to go to the column number of the value of the initial sheets <B1> cell to get my desired value from Sheet1.

any ideas without a separate vb function?

Whatever it is you're trying to do, it looks like you can do it without vb, using simple excel formulas. But, you need to rephrase your question, it is impossible to figure out what you are asking.
Yes, clarification required. If B1 first matches Sheet1!AY100, for instance, then MATCH returns 99, which cell value do you want the formula to return in that case? - which version of excel are you using?
i agree. i hate to ask but i didnt really know how to phrase this. thanks everyone
@user1732894, please be aware that stuffing the tag list full of the same words that are in your question title does not help people find your question. Tags are not keywords. Please select tags with care.

Gimp · Accepted Answer · 2012-10-09 20:17:14Z

Based off of what you're asking you can use the indirect formula to indirectly reference a cell via it's text name

=Indirect("Sheet1!" & "A1")

That will return the text from sheet 1's A1 cell.

You can implement this inside of your larger formula.

BUT

It may be easier to use the offset formula in this case.

=Offset(Sheet1!A1,0,5)

That formula will start at cell A1, and move it 5 cells to the right... so combined with your match formula you could do this..

 =IF(MATCH(B1,Sheet1!$AY$2:$AY$50000,0) > 0,Offset(Sheet1!A1,0,MATCH(B1,Sheet1!$AY$2:$AY$50000,0)-1),"")

Both of these approaches are valid ways to use a formula or text to arrive at a new cell.

however, I am unclear which approach best suits your needs due to the vagueness within your original post.

Note that if you have excel 2007+ you can simplify the offset as:

=Iferror(Offset(Sheet1!A1,0,MATCH(B1,Sheet1!$AY$2:$AY$50000,0)-1),"")

that's exactly what i needed. Thanks! Just had to kludge it around a bit. I really hate those questions that you know what you want to ask, but not how to google it properly.
MATCH returns a positive number or an error, so checking whether result is > 0 is redundant - if B1 isn't found your formula returns an error, it can never return a blank, the OFFSET part on its own would do the same thing - suggest you wrap that in IFERROR (assuming Excel 2007) to error-trap
great point for readability barry. I left it in a format for cross-version compatibility. I'll update with 2007+ for your comment / his benefit.

migrated from stackoverflow.com Oct 11 '12 at 7:08