Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

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

This question came from our site for professional and enthusiast programmers.

    
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. –  kreativitea Oct 9 '12 at 20:25
    
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? –  barry houdini Oct 9 '12 at 20:44
    
i agree. i hate to ask but i didnt really know how to phrase this. thanks everyone –  user1732894 Oct 9 '12 at 20:55
    
@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. –  Charles Oct 10 '12 at 1:43
add comment

1 Answer 1

up vote 0 down vote accepted

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),"")
share|improve this answer
    
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. –  user1732894 Oct 9 '12 at 20:51
    
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 –  barry houdini Oct 9 '12 at 20:54
    
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. –  Gimp Oct 9 '12 at 20:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.