Requesting Formula that prioritizes a set of IF formulas followed by a VLOOKUP

[Radiobeat33]

Hello All,

I have a series of IF functions that will plot certain phone charge rates. I have the formula based on priority. Here's an idea of what I want to do:

First Step: If the specified cell has 0 as the value, then 0 will be returned

Second Step: If that same specified cell isn't 0, then I have quite a few IF formulas to return a value based on ID numbers in a seperate column.

The third step i'm trying to implement is a VLOOKUP that pulls from a set of data if the specified cell returns neither 0, nor any of the ID Numbers. This will be set as a last resort, but I need it to do a VLOOKUP. I also need this to happen in order. I've added my current working formulas. I need to know if it is possible to combine these 2 formulas. I've attached the spreadsheet, please let me know if this is possible.

This code is in cell N4 of the first tab

Please Login or Register  to view this content.

This is the code from cell M4 I would like added in the end of the 1st code somehow. It pulls data from the second tab

Please Login or Register  to view this content.

Hopefully this will give you an idea of what i'm trying to do. Please advise if you need me to elaborate anymore.

Thanks in advance for checking this out!


Rate Sample.xlsx

[dilipandey]

Hi Radiobeat,

Welcome to the forum.

I have checked your attachment and got little confused. What is the relation between the formula in M4 and N4 because M is the master air rate and N is air rate difference.
Also, in N you are putting rates manually in formula based on E and H. Do you have any master table for these manual values.

I would suggest you to provide just a brief description of what you are trying to achieve. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[teylyn]

Hello,

the nested IF can be shortened by using an OR() function

Please Login or Register  to view this content.

The last nested IF statement has the letters "IF" in the False statement. What is this about?

How do you want to combine the Vlookup and the IF statement? The Vlookup returns the Master Air rate and the IF statement returns the Air rate difference. I don't understand how these would combine.

can you clarify?

cheers,

[Radiobeat33]

First off, thank you both for responding so promptly and I appreciate the warm welcome.

I have checked your attachment and got little confused. What is the relation between the formula in M4 and N4 because M is the master air rate and N is air rate difference.
Also, in N you are putting rates manually in formula based on E and H. Do you have any master table for these manual values.

Updated Rate Sample.xlsx

I'll try to explain it a different way. M4 represents the master sheet on the 2nd tab. Everything in Columns A-K is a report I've pulled from a clearinghouse website at my company and pasted directed onto the spreadsheet. The idea is to make sure our rates match using their format. I've reuploaded my spreadsheet with changes to alleviate some of the confusion. This sheet is supposed to compare their rates with our Master to make sure we have the same rates. The way I left the information on N4 was a mistake on my part. I have moved the contents to N2 instead. N4 now has a subtraction formula to compare rates. Most of the numbers in what is now N2 come from what I have filtered in tab 2 via cell B1.

Ultimately, I would like the contents of N2 and M4 combined and reflected in M4.

Priority of Formula:
(This is what is in N2 right now)
- If there is a "0" in the H4, I want the rate to come back 0 as well. That means there were no reports for that portion. Otherwise, it would return an incorrect value in what is now N4.
- If there is not a zero in H4, then it's time to look at the exception SIDs on our Master tab (tab 2) and compare it to the respective columns in Tab 1. These are located in columns C and E on each tab. All of the IF statements in cell N2 reflect those numbers. These rates override all other rates. Our partners in column H may have the same name listed on multiple rows, but they work with the SIDs from column C and E to differentiate the rates.
*These rules are all represented in the formula in cell N2*

I want to add this final rule as a last resort:

-There is no zero in H4 followed by none of the listed exception SID numbers in columns C or E. I want the formula for what is now in M4 to kick in. I need it to do a VLOOKUP from there.

Combined - [N2s formula > M4 formula]



I may have made it more complicated than it needs to be, hopefully this all helps.

Thanks again guys.

[teylyn]

how about

Please Login or Register  to view this content.

[Radiobeat33]

Works like a charm. I can't thank you enough!