Using MATCH function to replace #N/As

hear_no_evil

New Member
Joined
Aug 30, 2006
Messages
5
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.

Thanks in advance.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
Select your range range:

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows
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?
 

hear_no_evil

New Member
Joined
Aug 30, 2006
Messages
5
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

Please try in C4:

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
 

hear_no_evil

New Member
Joined
Aug 30, 2006
Messages
5
Hi

Please try in C4:

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 :biggrin:
 

Forum statistics

Threads
1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top