I need help index/match

[Rod]

I HAVE A FORMULA:

=INDEX(A1:B6, MATCH(C1, A1:A6,2))

ALL I GET IS #N/A

 

[Ken Wright]

Make the first array A1:A6 and not B6. Also, if the data in A1:A6 is not sorted, then you will
need to use the optional last argument in the MATCH function of 0 that specifies an exact match
must be found. If you don't want an exact match because you are looking for the closest number at
times, then the data needs to be sorted.

=INDEX(A1:A6,MATCH(C1,A1:A6,2))

 

[Bob Phillips]

Rod,

Do you have a value in C1 to MATCH against A1:A6, as that will give #N/A?

Also, as you are using the result of the MATCH against an multi-column array
I think you need to add a column number for the INDEX function, such as
=INDEX(A1:B6, MATCH(C1, A1:A6,2).2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

You need to take another look at INDEX. It appears that you have left out
the column.

 

[cdavis]

-----Original Message-----
I HAVE A FORMULA:

=INDEX(A1:B6, MATCH(C1, A1:A6,2))

ALL I GET IS #N/A



------------------------------------------------

~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
In Excel 2000 and older there is no match type of 2.

What are you using and are you sure there is a match type
of 2?

 

[Ken Wright]

Doh!!!! - The syntax is wrong (Serves me right for just copying and pasting), as has been pointed
out, there is no argument of 2 for the MATCH function. You get to choose from 1,0,-1 or nothing.