Sep 19 2021 03:13 PM
Hi everyone I am in desperate need of help. I am pulling my hair out. I am trying to do a nested if statement with 49 if's from a drop down list and it keeps saying the formula is missing an opening or closing parenthesis and when I add them it says I have too many
Sep 19 2021 03:14 PM
Sep 19 2021 04:01 PM
@Cheney4206 You can probably replace the nested IFs with a VLOOKUP or XLOOKUP formula.
Let's say you want to do this:
IF A1=1 THEN "one", IF A1=2 THEN "two", IF A1=3 THEN "three", etc. etc.
You'd create a table like this in for example K1:L50:
1 | one |
2 | two |
3 | three |
4 | four |
... | ... |
You can then use
=VLOOKUP(A1, K1:L50, 2, FALSE)
to look up the value of A1 in column K and return the corresponding value from column L.
Sep 19 2021 04:28 PM
Sep 19 2021 04:30 PM
Sep 19 2021 08:36 PM - edited Sep 19 2021 08:37 PM
but it's always J8 times something, is that right?
In that case you can still use the suggestion from Hans Vogelaar to accomplish the desired result with far less pain. Whatever is selected from that drop down is used as a reference in the VLOOKUP with a formula like
=J8 * VLOOKUP(dropdownselection,TableOfValues,2,0)
ALL OF THE TEXTBOOKS on Excel warn against trying to nest too many IFs in a single formula; it becomes (as you have discovered) utterly unwieldy, to say nothing of unintelligible, un-maintainable...etc.