Simpler way than 10+ nested IF statements?

1. Simpler way than 10+ nested IF statements?

I've been stuck on this for hours, any excel wizards know of a way to simplify this problem?

I have a spreadsheet that calculates the gas rate based on what area it is located in (there are 24 areas). Instead of making multiple nested if statements with vlookups embedded, is there any easier way to do this? Perhaps VBA or an array function?

See attached excel spreadsheet. Gas rate is calculated in column E3.

Forecasting Master Excel Help.xlsx

Thank you

There is

Kind regards
Leo

en the file

little error

4. Re: Simpler way than 10+ nested IF statements?

Try the attached file on sheet2.
I changed the lookup area and the formula.

5. Re: Simpler way than 10+ nested IF statements?

Originally Posted by ncmay
Try the attached file on sheet2.
I changed the lookup area and the formula.
Wow this is great thank you! What would you do if the lookup value columns are all irregular in size? See tab 1 in this file attached below.

Forecasting Master Excel Help2.xlsx

6. Re: Simpler way than 10+ nested IF statements?

LeoTaxi and ncmay, Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

7. Re: Simpler way than 10+ nested IF statements?

jobinv, try this, cvopied down...
=INDEX(\$H\$3:\$AA\$14,MATCH(\$D2,OFFSET(\$H\$1,2,MATCH("Area "&\$A2,\$H\$1:\$AA\$1,0)-1,50,1)),MATCH("Area "&\$A2,\$H\$1:\$AA\$1,0)+1)
(I think your last answer - 5900 - is wrong. That value is against 0.62, not 0.75)

There are currently 1 users browsing this thread. (0 members and 1 guests)