Nested if statement and vlookup problem

[Guest]

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,IF(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

(e-mail address removed)

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

 

[Guest]

Hi Steve

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

Could it be a format Issue?

 

[Guest]

The reference cells, the combo box refers to contains text,
ie
Cell A1 fieldBoss 10" STD riser
Cell A2 fieldBoss 8" STD riser

etc
could that have anything to do with it?

 

[Dave Peterson]

It kind of looks like you're using a dropdown from the Forms toolbar on the
worksheet--with the input range assigned to A1:A13 and the cell link assigned to
E2.

If that's the case, then I'd use this formula to return the value chosen in the
dropdown:

=IF(E2=0,"Select an entry",INDEX($A$1:$A$13,E2))

=======
An alternative to using a dropdown from the Forms toolbar (or a combobox from
the control toolbox toolbar) would be to use Data|Validation.

It looks pretty similar, but is easier to implement.

Debra Dalgleish has lots of tips:
http://contextures.com/xlDataVal01.html

 

[Guest]

I dont think that is the problem
Have just run the example again using - fieldBoss 10" STD riser
incrementing the 10" from 1 - 13 in cells A1 - A13

all 3 formulae return expected results
ie Formula 1 returns 1 - 7 on the 1st 7 choices then FALSE ater that
Formula 2 - choice 1 - 7 = FALSE, Choice > 8 returns 8 to 13

Formula 3 returns the number every time

Have you tried running Formula Auditing on your 3rd IF formula when it
returns #VALUE ?

 

[Guest]

I forgot about the forms Option
Steve I assumed you were using data validation, if not refer to Dave's post

thanks for reminding me Dave

 

[Guest]

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]

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.

 

[Guest]

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.