# Using MATCH function to replace #N/As

#### hear_no_evil

##### New Member
I have a list of data like the following:

#N/A, #N/A, 5, 20, 55, 10, #N/A, #N/A, 17, #N/A, #N/A

I would like to replace all #N/As with the next value to the right that is a valid number. However if the end value is #N/A then I want to use last non-#N/A value on the right hand side.

So my data would become:

5, 5, 5, 20, 55, 10, 10, 10, 17, 17

I'm looking for one cell that would hold the formula, if possible.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Edit
Goto
Special
Formula
Tick on Errors

This will select all the cells with "NA"

Now enter "=" and point to the cell on the Right

End by clicking on Ctrl+Enter

Eli

I have a list of data like the following:

#N/A, #N/A, 5, 20, 55, 10, #N/A, #N/A, 17, #N/A, #N/A

I would like to replace all #N/As with the next value to the right that is a valid number. However if the end value is #N/A then I want to use last non-#N/A value on the right hand side.

So my data would become:

5, 5, 5, 20, 55, 10, 10, 10, 17, 17
From your written description, I would have thought the result of your sample would be:
5, 5, 5, 20, 55, 10, 17, 17, 17, 17,17?

I'm looking for one cell that would hold the formula, if possible.
Can you explain this in more detail?

5, 5, 5, 20, 55, 10, 10, 10, 17, 17
From your written description, I would have thought the result of your sample would be:
5, 5, 5, 20, 55, 10, 17, 17, 17, 17,17?

I'm looking for one cell that would hold the formula, if possible.
Can you explain this in more detail?

Yes, apologies it should have been
5, 5, 5, 20, 55, 10, 17, 17, 17, 17,17

I can do it if I use multiple columns etc etc with basic formulas in each one. However I am looking for a formula that would work in just one cell.

Hi

Code:
``=IF(ISNUMBER(C3),C3,IF(COUNT(C3:\$M\$3),INDEX(C3:\$M\$3,1,MATCH(TRUE,ISNUMBER(C3:\$M\$3),0)),INDEX(\$C\$3:\$M\$3,1,MAX(IF(ISNUMBER(\$C\$3:\$M\$3),COLUMN(\$C\$3:\$M\$3)-COLUMN(\$C\$3)+1)))))``
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy right

Hope this helps
PGC
Book2
ABCDEFGHIJKLMNO
1
2
3#N/A#N/A5205510#N/A17#N/A#N/A#N/A
45552055101717171717
5
6
Sheet1

Hi

Code:
``=IF(ISNUMBER(C3),C3,IF(COUNT(C3:\$M\$3),INDEX(C3:\$M\$3,1,MATCH(TRUE,ISNUMBER(C3:\$M\$3),0)),INDEX(\$C\$3:\$M\$3,1,MAX(IF(ISNUMBER(\$C\$3:\$M\$3),COLUMN(\$C\$3:\$M\$3)-COLUMN(\$C\$3)+1)))))``
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy right

Hope this helps
PGC
Book2
ABCDEFGHIJKLMNO
1
2
3#N/A#N/A5205510#N/A17#N/A#N/A#N/A
45552055101717171717
5
6
Sheet1

Great, this works thanks

Replies
3
Views
241
Replies
2
Views
65
Replies
8
Views
422
Replies
6
Views
315
Replies
4
Views
352

1,217,750
Messages
6,138,410
Members
450,134
Latest member
TYoung24

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back