# 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.

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.

• 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 '14 at 12:52
• It's "impicit intersection" - see this link myonlinetraininghub.com/excel-implicit-intersection ......or google for others – barry houdini Feb 9 '14 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 '14 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 '14 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 `?`