#N/A Error with INDEX MATCH formula

**paperwings25** · 04-04-2012, 01:58 PM

I used the same formula as the previous quarter that worked. I copy and pasted new data(from same source that previous quarter data came from) and I am receiving all #N/A errors. I used the same file and renamed the file to 12Q1 rather than the 11Q4 last quarter. The formula is below. I have checked all the referencing cells in the formula and they are all correct. I just do not know why I am receiving #N/A. Did the data possibly come in as text rather than number, therefore it isn't recognizing the "match"? Any help would greatly be appreciated. Thanks!

=INDEX('TBSTATMG UC drilled'!$B$6:$U$938,MATCH($A7,'TBSTATMG UC drilled'!$B$6:$B$938,0),MATCH($D$3,'TBSTATMG UC drilled'!$B$6:$U$6,0))

**dilipandey** · 04-04-2012, 02:02 PM

Hi paperwings..

check if A7 exists in B6:B938 or D3 exists in B6:U6..
post the sample file if you are stuck.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**ChemistB** · 04-04-2012, 02:05 PM

One way to tell if the value in A1 is text is, in a blank cell type = ISNUMBER(A1), False means text
Check both your lookup value and your expected match.

**paperwings25** · 04-04-2012, 02:15 PM

Originally Posted by ChemistB

One way to tell if the value in A1 is text is, in a blank cell type = ISNUMBER(A1), False means text
Check both your lookup value and your expected match.

I did that. The "TBSTATMG UC drilled" tab has text rather than number for the account number. This would be the first match formula. ($A7,'TBSTATMG UC drilled'!$B$6:$B$938,). The $B$6:$B$938 is the range that has the text when I did that formula check you posted. $A7 is a number. How do I change all these texts to numbers?

**ChemistB** · 04-04-2012, 02:32 PM

Assuming there are no extra hidden characters or blanks, try this

In a blank cell, enter 1, then copy it.
Select your range B6:B938, Paste Special> Multipy> OK
Check your results. (Multiplying a number stored as text by 1 results in Excel recognizing that entry as a number.)
Did that work?

**paperwings25** · 04-04-2012, 02:36 PM

I believe it did. Some #N/A's turned to numbers, but some remained as #N/A. I am not going through and verifying that the cells that have #N/A are in the table range of B6:B938.