Hi folks
I am having a problem with nested if statements, and vlookup.
I have a combo box to select an item. The combo box is liked to a group of
cell, where the date for it is kept. the list is 13 items long. I then
perform a set of nested IF statements to work out a number for the item
selected.

this line is for the first half
=IF(E2=A1,1,IF(E2=A2,2,IF(E2=A3,3,IF(E2=A4,4,IF(E2 =A5,5,IF(E2=A6,6,IF(E2=A7,7)))))))
this line is for the next half, there are no macro's on this sheet. and it
is virus free.

thank you to anyone who is able to help me out
=IF(E2=A8,8,IF(E2=A9,9,IF(E2=A10,10,IF(E2=A11,11,I F(E2=A12,12,IF(E2=A13,13))))))
and this line selects the number
=IF(E5,E5,E6)

E2 holds the combo box
the data for the combo box resides in A1:A13

Here is the problem.
the value returned from the the selection formula either comes up with
#VALUE! or the correct number.
I then perform a Vlookup to get some more data and I either get a return of
#N/A or #VALUE!.

I have spent the majority of a day trying different ways of doing this but
still have this problem. I have an XLS worksheet available by email if you
want to see the problem in action. Please just email me at



and I'll be more than happy to email it to you

Hi Steve

Having just run your example using basic information, I return the correct
information everytime.

Could it be a format Issue?

"Steve" wrote:

Hi folks
I am having a problem with nested if statements, and vlookup.
I have a combo box to select an item. The combo box is liked to a group of
cell, where the date for it is kept. the list is 13 items long. I then
perform a set of nested IF statements to work out a number for the item
selected.

this line is for the first half
=IF(E2=A1,1,IF(E2=A2,2,IF(E2=A3,3,IF(E2=A4,4,IF(E2 =A5,5,IF(E2=A6,6,IF(E2=A7,7)))))))
this line is for the next half, there are no macro's on this sheet. and it
is virus free.

thank you to anyone who is able to help me out
=IF(E2=A8,8,IF(E2=A9,9,IF(E2=A10,10,IF(E2=A11,11,I F(E2=A12,12,IF(E2=A13,13))))))
and this line selects the number
=IF(E5,E5,E6)

E2 holds the combo box
the data for the combo box resides in A1:A13

Here is the problem.
the value returned from the the selection formula either comes up with
#VALUE! or the correct number.
I then perform a Vlookup to get some more data and I either get a return of
#N/A or #VALUE!.

I have spent the majority of a day trying different ways of doing this but
still have this problem. I have an XLS worksheet available by email if you
want to see the problem in action. Please just email me at



and I'll be more than happy to email it to you

Hi Steve, Dave
my apologies, it is a Data/Validation that I am using. You're right they are
very similar.

Thanks for your help with the nested if function, all that is holding me
back is the problem with the VLOOKUP.
I've tried using vlookup to return the value in another column, which is a
numeric value. but I got the error returns.

If you're getting a #n/a error back from your =vlookup() formula, then there is
not a match in the table for what you're looking for.

You know what cell should match up, you can use something like:

=e2=a7
(for instance)

If you get True, then I don't have an explanation.
If you get false, then maybe you'll be able to look at each cell and see the
difference.


Steve wrote:

Hi Steve, Dave
my apologies, it is a Data/Validation that I am using. You're right they are
very similar.

Thanks for your help with the nested if function, all that is holding me
back is the problem with the VLOOKUP.
I've tried using vlookup to return the value in another column, which is a
numeric value. but I got the error returns.

--

Dave Peterson

I understand what you are saying, but I can't see how there can't be a match.
I have vlookup reading the value produced by the nested IF's and when there
isn't an error in the nested IF's I get a false reading from the Vlookup.
in the data/validation cell I have selected "selection 13" and the value
from range b1:c13 column 2 I should get a value of 0129288 which is a product
code, but I get #N/Aas the result. (column 1 (b1:b13) reads 1 to 13.

Another prob with vlookup is if I direct the value to check up on, directly
to the data/validation cell, it will read it as far as selection 9 and it
will return a value of 1 to 9 depending on the selection it reads. ie
selection 4 returns a 4 from column 2 (b1:b13) and 0128710 from column 3
(c1:c13), but then the next four (selection 10 to selection 13) will return
a value of 1 no matter if it is selection 10, 11, 12 or 13.


"Dave Peterson" wrote:

If you're getting a #n/a error back from your =vlookup() formula, then there is
not a match in the table for what you're looking for.

You know what cell should match up, you can use something like:

=e2=a7
(for instance)

If you get True, then I don't have an explanation.
If you get false, then maybe you'll be able to look at each cell and see the
difference.


Steve wrote:

Hi Steve, Dave
my apologies, it is a Data/Validation that I am using. You're right they are
very similar.

Thanks for your help with the nested if function, all that is holding me
back is the problem with the VLOOKUP.
I've tried using vlookup to return the value in another column, which is a
numeric value. but I got the error returns.

--

Dave Peterson