Index Match #N/A error, please help

When I do the following

=INDEX(Sheet2!A3:D319,MATCH(SCHEDULING!B3,Sheet2!A3:A319,0),4)

I get this error

#N/A


Could someone please help me with this.

One reason might be this, but I don't know how to fix it.
In the MATCH(Scheduling B3) it is a number format.

In the Sheet2 column A field it was imported as a text format and i changed it to a number format. However after I changed it to a number format, and i went and retyped the number it worked, but it left a green arrow in the upper left corner of the cell with the formula.

So i might be able to retype these numbers to get them to work, but I dont really want to retype 1000 numbers every time the data refreshed because it is importing as text.

Any help would be great


Thanks
Josh

It is true that a number (e.g. 42) will not match a text value (e.g. "42").
But try this:
=INDEX(Sheet2!A3:D319,MATCH(SCHEDULING!B3,VALUE(Sheet2!A3:A319),0),4)
and use Ctrl-Shift-Enter to enter this as an array formula
--
- K Dales


"jhahes" wrote:

>
> When I do the following
>
> =INDEX(Sheet2!A3:D319,MATCH(SCHEDULING!B3,Sheet2!A3:A319,0),4)
>
> I get this error
>
> #N/A
>
>
> Could someone please help me with this.
>
> One reason might be this, but I don't know how to fix it.
> In the MATCH(Scheduling B3) it is a number format.
>
> In the Sheet2 column A field it was imported as a text format and i
> changed it to a number format. However after I changed it to a number
> format, and i went and retyped the number it worked, but it left a
> green arrow in the upper left corner of the cell with the formula.
>
> So i might be able to retype these numbers to get them to work, but I
> dont really want to retype 1000 numbers every time the data refreshed
> because it is importing as text.
>
> Any help would be great
>
>
> Thanks
> Josh
>
>
> --
> jhahes
> ------------------------------------------------------------------------
> jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
> View this thread: http://www.excelforum.com/showthread...hreadid=474267
>
>