3

enter image description here

Why doesn't this work? I tried checking for whitespace, made sure length was the same, etc. Driving me nuts! I just want Alabama! The error given is "Value not available".

pnuts
  • 58,317
  • 11
  • 87
  • 139
atkayla
  • 8,143
  • 17
  • 72
  • 132
  • 3
    the order of your column is wrong. The leftmost column must contain the value you are matching. what you are doing is looking for `AL` in the `State Name` column. of course, excel can't see it and thus returns `#N/A!`. – L42 Feb 07 '14 at 02:53

4 Answers4

10

The usual solution is to apply =MATCH to find the row number in the array: 

=MATCH(D1,B:B,0)

will search for the value in D1 in ColumnB (the last 0 means exactly) and hopefully return 2 (the second row in the chosen array - ie the whole of ColumnB). This can then be fed into: 

=INDEX(A:A,MATCH(D1,B:B,0))

where it becomes the second row of ColumnA, ie Alabama

Details here =MATCH =INDEX and also http://www.excelhero.com/blog/2011/03/the-imposing-index.html

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    plus one for explanation and tutorial links :D – L42 Feb 07 '14 at 03:05
  • 1
    I used to have a sticky note on my monitor that said `=index(range, row, column)` and `Match(what,row or column, 0 for exact or 1 for nearest)`. That helped internalise the syntax and wean me off Offset(). – teylyn Feb 07 '14 at 03:11
  • @pnuts there is no need to delete. As what Tim Williams said, having the same answer is not a waste but rather a way to show the OP that the presented solution is indeed true and correct. – L42 Feb 07 '14 at 03:13
4

The order of your column is wrong. The leftmost column must contain the value you are matching. What you are doing is looking for AL in the State Name column. Of course, excel can't see it and thus returns #N/A!.

You can try this:

=INDEX($A:$A,MATCH(D1,$B:$B,0),1)

Hope this helps.

L42
  • 19,427
  • 11
  • 44
  • 68
  • 1
    +1, but also note that in a range with just one column or just one row, Index works fine without the third parameter. If the range is a column, just do =index(column, number). If the range is a row, then just do =index(row, number). The third parameter is only required in a range with several columns AND several rows. Then the second parameter specifies the row and the third parameter the column. – teylyn Feb 07 '14 at 03:05
  • @teylyn is right. The reason why i completed the syntax is for the OP to get a hang of the formula. :D btw, pnuts provided a complete explantion and link on how to use the functions. – L42 Feb 07 '14 at 03:07
0

The formula looks for "AL" in the first column specified (column A). It only finds "Alabama", so returns error.

Roberto
  • 2,054
  • 4
  • 31
  • 46
0

It can be slightly easier, even:

=INDEX(A:A,MATCH(D1,B:B))
unigeek
  • 2,656
  • 27
  • 27