Why is this Excel formula returning two different values?

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.

Lorae

Posted 2014-02-08T19:36:35.643

Reputation: 31

Answers

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.

Jerry

Posted 2014-02-08T19:36:35.643

Reputation: 4 716

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 – 2014-02-09T12:52:02.993

It's "impicit intersection" - see this link http://www.myonlinetraininghub.com/excel-implicit-intersection ......or google for others

– barry houdini – 2014-02-09T13:20:58.880

@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 – 2014-02-09T13:40:10.600

@barryhoudini that is blowing my mind, I'm going to have to find out why MS thinks this is a good thing. Thanks! – Raystafarian – 2014-02-10T13:37:18.473

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 ?

barry houdini

Posted 2014-02-08T19:36:35.643

Reputation: 10 434