# Thread: Formula that returns only value from a cell that is not N/A

1. ## Formula that returns only value from a cell that is not N/A

Hi, I have a table like below and need to collect values that are not n/a in a snigle column. How can I do it with formula or some macro?
 #N/A #N/A #N/A 570333.3 #N/A #N/A #N/A #N/A 570333.3 #N/A #N/A 507577 #N/A #N/A #N/A #N/A #N/A 507577 #N/A #N/A #N/A #N/A #N/A #N/A 577621.8 #N/A 577621.8 #N/A #N/A #N/A 389439.3 #N/A #N/A #N/A #N/A 389439.3 #N/A #N/A 705569.2 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 715811.6 #N/A #N/A #N/A

2. if there's only one value not #N/A in the row:
=INDEX(A1:H1,MATCH(9E+99,A1:H1,1))
(it actually returns the rightmost non-#N/A value)

3. What do you mean by 'collect values'?

4. Originally Posted by p45cal
if there's only one value not #N/A in the row:
=INDEX(A1:H1,MATCH(9E+99,A1:H1,1))
(it actually returns the rightmost non-#N/A value)
It works no matter I don't understand this 9E+99... Thanks :-)

5. probably my expression wasn't correct...what I needed was take the value that is not N/A and place it in a separete column so I could get all values in one column.

6. Originally Posted by jmaricic
I don't understand this 9E+99... Thanks :-)
It's a big number, probably bigger than any other value on the row, which it tries to get a match for. Check out the Help for the Match function.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•