Index and match

[ckelly12]

Hi There,

I have 10 rows and 8 columns of data, I would like to look up a number
in the data and return the number in the first column, what is the
formula for this? I tried this:

=INDEX(B6:I28,MATCH(O5,C6:I28,0),1)

but it returns #N/A, can anyone help?

Thanks
Colleen

 

[Pete_UK]

It would help if you could give an example of what your data is like
and what you are trying to achieve, because it is not too clear to me.
Is your data sorted in any way? Are you looking for an exact match?

However, the formula will fail because the MATCH function works on a
one-dimensional range like C6:C28. I'm not sure why you need that
range if you only have 10 rows of data.

Hope this helps.

Pete

 

[joeu2004]

I would like to look up a number in the data and return the
number in the first column, what is the formula for this? I tried this:
=INDEX(B6:I28,MATCH(O5,C6:I28,0),1)
but it returns #N/A, can anyone help?

There may be several things going on here.

First, I believe that MATCH requires an array of only a single column
or a single row. The XL2003 help page does not say as much. But my
experiments suggest it.

Second, if you intend to copy the formula down and across columns and
rows, you should use absolute references for the invariant parts. For
example:
=INDEX($B$6:$B$28,MATCH(O5,$C$6:$B$28,0),1)

Finally, MATCH will return #N/A if there is no match. If that is a
possibility, you might want something like:
=IF(ISNUMBER(MATCH(O5,$C$6:$B$28,0)),
INDEX($B$6:$B$28,MATCH(O5,$C$6:$B$28,0),1),"")


PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.