IF statement more than 64; Macro?

Question

Hello thanks for taking a moment to try to help me.

I am working on a spreadsheets that I am hoping to simplify.

What I am looking for is a function that will read a cell and then imput a value into another cell depending on whats in the first.

I was hoping to use a simple if statement string however I have about 120 statements. Quite more than the nesting limit of 64.

Here is an example:

County ID -- Fee

If "County ID" = Lee, Fee =48.50

If "County ID" = Howard, Fee = 33.50

--Continued for more county names and fees--

I thought possibly to make two macros that would do 64 counties and then just run one than the other, I fear that would cause error or risk of missing something.

oliverscheurich · Answer

Dhardingsea&nbsp;You can apply INDEX / MATCH or VLOOKUP. If you work with Excel 2016 or later you can apply XLOOKUP. All options are much easier to set up and to maintain compared to nested IF (or IFS) statements.&nbsp;

peterbartholomew1 · Answer

Dhardingsea&nbsp;If the number of nested IFs goes beyond 4, there is probably something wrong with your approach!&nbsp; The best approach is likely to be a lookup.&nbsp; For example&nbsp;= XLOOKUP(county, feeTbl[County], feeTbl[Fee])&nbsp;&nbsp;

peterbartholomew1 · Answer

Agreed.

Forum Discussion

IF statement more than 64; Macro?

Share

Resources