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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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
Back
Top