For the following array which has been named "test":

1 a
2 b
3 c
4 d
5 e

I wrote the following if and lookup functions in cell A1, "=IF(A2 = " ", "
", VLOOKUP(A2, test, 2, FALSE))". What I want is Cell A1 to return a value
from the "test" array. For example, "a" should be returned when 1 is entered
in A1. Also, I want A1 to be blank when A2 is blank. The vlookup is working
fine but when A2 is blank, A1 is showing "#N/A". Tried everything but nothng
works. Please help.
Thanks!

Your question isn't clear. Where are you entering this formula?

On the one hand you want a to be returned when A1=1, but you also want A1 to
be blank when A2 is blank.

You can't have your cake and eat it too.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Daviv" wrote:

For the following array which has been named "test":

1 a
2 b
3 c
4 d
5 e

I wrote the following if and lookup functions in cell A1, "=IF(A2 = " ", "
", VLOOKUP(A2, test, 2, FALSE))". What I want is Cell A1 to return a value
from the "test" array. For example, "a" should be returned when 1 is entered
in A1. Also, I want A1 to be blank when A2 is blank. The vlookup is working
fine but when A2 is blank, A1 is showing "#N/A". Tried everything but nothng
works. Please help.
Thanks!

Hi,

Maybe:

=IF(ISNA(VLOOKUP(A2, test, 2, FALSE)),"",VLOOKUP(A2, test, 2, FALSE))

HTH
Jean-Guy


"Daviv" wrote:

For the following array which has been named "test":

1 a
2 b
3 c
4 d
5 e

I wrote the following if and lookup functions in cell A1, "=IF(A2 = " ", "
", VLOOKUP(A2, test, 2, FALSE))". What I want is Cell A1 to return a value
from the "test" array. For example, "a" should be returned when 1 is entered
in A1. Also, I want A1 to be blank when A2 is blank. The vlookup is working
fine but when A2 is blank, A1 is showing "#N/A". Tried everything but nothng
works. Please help.
Thanks!

Pinmaster,

After I use your formula, both A1 and A2 are blank.

David F

The formula is in Cell A1. When I enter a number in cell A2, say 1, the
formula in A1 should return from the "test" array "a". That's what I am
trying to accomplish with IF(A2 = " ", " ", VLOOKUP(A2, test, 2, FALSE)).
The IF function was added because I wanted A1 to be blank if A2 is blank, if
nothing has been inputted. However, I am getting #N/A.
--
Thanks!


"pinmaster" wrote:

Hi,

Maybe:

=IF(ISNA(VLOOKUP(A2, test, 2, FALSE)),"",VLOOKUP(A2, test, 2, FALSE))

HTH
Jean-Guy


"Daviv" wrote:

For the following array which has been named "test":

1 a
2 b
3 c
4 d
5 e

I wrote the following if and lookup functions in cell A1, "=IF(A2 = " ", "
", VLOOKUP(A2, test, 2, FALSE))". What I want is Cell A1 to return a value
from the "test" array. For example, "a" should be returned when 1 is entered
in A1. Also, I want A1 to be blank when A2 is blank. The vlookup is working
fine but when A2 is blank, A1 is showing "#N/A". Tried everything but nothng
works. Please help.
Thanks!

Pinmaster,

Your formula works. I entered the wrong array name. You are beautiful!

david
--
Thanks!


"Daviv" wrote:

Pinmaster,

After I use your formula, both A1 and A2 are blank.

David F

The formula is in Cell A1. When I enter a number in cell A2, say 1, the
formula in A1 should return from the "test" array "a". That's what I am
trying to accomplish with IF(A2 = " ", " ", VLOOKUP(A2, test, 2, FALSE)).
The IF function was added because I wanted A1 to be blank if A2 is blank, if
nothing has been inputted. However, I am getting #N/A.
--
Thanks!


"pinmaster" wrote:

Hi,

Maybe:

=IF(ISNA(VLOOKUP(A2, test, 2, FALSE)),"",VLOOKUP(A2, test, 2, FALSE))

HTH
Jean-Guy


"Daviv" wrote:

For the following array which has been named "test":

1 a
2 b
3 c
4 d
5 e

I wrote the following if and lookup functions in cell A1, "=IF(A2 = " ", "
", VLOOKUP(A2, test, 2, FALSE))". What I want is Cell A1 to return a value
from the "test" array. For example, "a" should be returned when 1 is entered
in A1. Also, I want A1 to be blank when A2 is blank. The vlookup is working
fine but when A2 is blank, A1 is showing "#N/A". Tried everything but nothng
works. Please help.
Thanks!