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.

I am attempting to use Excel (2013) to look up whether the value 1 turns up in a given set (using the CHOOSE() function, then, if so, printing the value in the cell directly to the right of it using the VLOOKUP() function. Why would this formula yield two different results in different cells when the formula itself is identical?

=IF(CHOOSE(1, $A$4:$A$17) = 1, VLOOKUP(1, $A$4:$C$17, 2, FALSE), "?")

Cells V4 to V7 in my sheet all have the exact same formula (the one printed above), yet they are returning different values. V4's returned value is the type I would like (i.e., the value of cell B4, which is a number), yet the others are returning "?". Why is this and how can I fix it? Thanks in advance for any help.

share|improve this question

2 Answers 2

Let's say there are those values in the range A4:A6:

1
2
3

When you have CHOOSE(1, $A$4:$A$17).

Arrays in excel will evaluate to the corresponding row or column (whichever it is depends on the situation) the formula is in if evaluated normally. If the formula is in row 4, then CHOOSE(1, $A$4:$A$17) will get CHOOSE(1, $A$4). In row 5, it will get CHOOSE(1, $A$5). This is because CHOOSE normally doesn't take array values.

If you want to get a list out of the range $A$4:$A$17, you will have to enter CHOOSE as an array formula, and since it is in a bigger formula, that applies too. You will see it work like you expect it to if you use Ctrl+Shift+Enter after inserting the formula and instead of pressing Enter alone.

share|improve this answer
    
Okay, this is correct, but I still don't understand why. Do you have any more clarification? choose(1,A$1,A$4) in b1 = a1; same formula in b4 = a4. I guess what I'm saying is, good question, good answer, any sources as to why it evaluates based on position? –  Raystafarian Feb 9 at 12:52
1  
It's "impicit intersection" - see this link myonlinetraininghub.com/excel-implicit-intersection ......or google for others –  barry houdini Feb 9 at 13:20
    
@Raystafarian See barry's link above this comment for explanation. And I just realised I didn't really address the 'how can I fix it' part, especially since CHOOSE isn't the appropriate function, but then, barry has it already down. –  Jerry Feb 9 at 13:40
    
@barryhoudini that is blowing my mind, I'm going to have to find out why MS thinks this is a good thing. Thanks! –  Raystafarian Feb 10 at 13:37

I'm not clear why you would use CHOOSE function here, I'd suggest that more usually you'd check with COUNTIF, i.e.

=IF(COUNTIF($A$4:$A$17,1)>0,VLOOKUP(1,$A$4:$C$17,2,FALSE),"?")

....but in Excel 2007 (or newer) it's possibly easier to use IFERROR function like this:

=IFERROR(VLOOKUP(1,$A$4:$C$17,2,FALSE),"?")

Both of those formulas will return the result of the VLOOKUP if 1 appears in A4:A17....otherwise ?

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.