I need help index/match
 

I HAVE A FORMULA:

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

ALL I GET IS #N/A



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I need help index/match
 

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))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Rod" wrote in message ...
I HAVE A FORMULA:

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

ALL I GET IS #N/A



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003

I need help index/match
 

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)

"Rod" wrote in message
...
I HAVE A FORMULA:

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

ALL I GET IS #N/A



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I need help index/match
 

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

"Rod" wrote in message
...
I HAVE A FORMULA:

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

ALL I GET IS #N/A



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I need help index/match
 

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

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

ALL I GET IS #N/A



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ 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?

I need help index/match
 

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.
:-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Ken Wright" wrote in message
...
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))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Rod" wrote in message ...
I HAVE A FORMULA:

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

ALL I GET IS #N/A



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003