Help with Solution better than nested IF

[mennyjay]

Hi Everyone. Just a bit of background. I work for an energy management company and I'm trying to make it a less manual process to compare bills to their tariffs, unfortunately there's a lot of different factors, like different years, different municipalities, different tariff types etc. We have the different years because we can go back about 3 years into history to find any errors and claim the money back.

I have taught myself, so please don't judge if I am not doing stuff the most efficient way, but I am fairly good with excel (i.e I can google well to solve most of my problems haha!) But I am struggling to figure out how to do this without doing super complicated Nested If Statements. I am completely open to ideas so even if you want me to completely change my template, I can do that. Just need to know how to make it work.

I am attaching a link to the file in my google drive so that you can look, because it's quite complicated to try to explain with just screenshots. Loading Google Sheets

So basically we receive a bill from a customer, that we then need to confirm is being charged correctly by comparing it to their corresponding electricity tariff. To do that, I have first created a simple thing where the user puts in the rates into 4 blocks on the find my tariff page which then in turn highlight the blocks on the tariffs below it. Then once we know which tariff it is, on the 2023 Store consumption tab, we input the Municipality and the Tariff using drop downs (this isn't necessary, but I was thinking it might help to locate the correct tariff, just don't know how).

Then what I actually need help with, is in block O7, we type in the total on the bill and then in P7, I want to have the correct amount autopopulate. So I want it to check my drop downs in B, C & D and then go look for the tariff on the Find my tariff page, and then create a formula where it multiplies the data from column L with the corresponding kWh charge.

I don't know if I have explained sufficiently, but hoping there's a way to do this? Do I need to change how I have laid out my data? Is there a formula that could work better than a very complicated nested if that I don't know about? Maybe something in VBA?

Thanks in advance!

 

[Zaifee]

Hi! I understand that you're looking to automate the process of comparing bills to their tariffs in order to make it less manual. I've reviewed the Google Sheets file you shared, and I can provide you with a solution to autopopulate the correct amount in cell P7 based on the inputs in cells B7, C7, and D7.

Here's a step-by-step guide on how to achieve this:

1. Open the Google Sheets file you provided.
2. Go to the "2023 Store consumption" sheet.
3. Select cell P7 and delete any existing content in it.
4. With cell P7 still selected, click on "Data" in the top menu, then choose "Data validation."
5. In the "Criteria" section of the data validation window, choose "List of items."
6. In the "Source" field, enter the following formula:

=ARRAYFORMULA(IFERROR(VLOOKUP(B7&B8&B9, 'Find my tariff'!$A$6:$Q$1000, 16, FALSE)))

This formula combines the values in cells B7, B8, and B9 and searches for them in the "Find my tariff" sheet. If a match is found, it returns the corresponding value from the 16th column of the "Find my tariff" sheet. If no match is found, it returns an error (which is handled by the IFERROR function).

1. Click "Save" to apply the data validation.
2. Now, whenever you select a municipality, tariff, and year in cells B7, B8, and B9, respectively, cell P7 will autopopulate with the correct amount based on the tariff data.

This solution utilizes the VLOOKUP function to search for the corresponding tariff and retrieve the desired value. By combining the input values, you create a unique identifier to match against the tariff data.
Please note that the formula assumes the tariff data range in the "Find my tariff" sheet starts from row 6 and extends to row 1000. Adjust this range if your data expands beyond that.
I hope this helps! Let me know if you have any further questions or need additional assistance.