# If function

• Alright so i did a vlookup and have a long list of numbers in cloumn A and the results of the vlookup in column B, Some of the values in column B say "#N/A"
I want to return the value in cloumn A if Column B says "#N/A" and if it doesnt say NA i want the cell to remain blank.

I tried =if(B1="#N/A",A1) this still returns an #N/A and i dont know how to proceed.

Further more id like to get rid of those blanks between the numbers if at all possible but i can do this manually if there is no easy way to do it.

• Re: If function help

sometyhinglike this for B2

=if(NA(vlookkup(A2,,,,,,,,,,,,),A2.vlookip(A2,..............)

can you write the formula

I am not an expert. better solutions may be available. \$\$\$venkat\$\$\$1926@\$\$\$gmail.com

• Re: If function help

i want the if separate from the vlookup in column C im not looking to change the NA in cloumn B rather have cell A returned in Cell C when B is NA

• Re: If function help

Try
=IF(ISERROR(B1),IF(ISERR(B1), "", A1),"")

#N/A in a cell indicates that an error value is in the cell, B1="#N/A" tests for a particular text value in the cell.
ISERROR returns TRUE for any error value, ISERR returns TRUE for any error value except #N/A

Also = is a comparison operator for numbers, text or logical values, not error values. Using it to test an error value will create its own error.

• Re: If function help

Works perfectly! thanks for explaining to me the difference between the Iserr or NA as well much apprechiated!

• Re: If function help

One more thing, say i were to copy the values to another sheet where there are only the returned numbers but theres like 6000 cells with varying sized gaps between each value is there any easy way to delete all the blank cells inbetween those values?

• Re: If function

Select the range.
Use Edit>Goto>Special>Blank Cells to select the blank cells

Delete the cells (don't press the Delete button on your keyboard, use the Excel menu)

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!