The formula below is checking for a typed entry in G32 which can either read Basic,Lifeline or Courtesy and each of those will have their own set of arguments depending on what I5 reads. Its not working for me can you please help?

=IF(AND(G32="basic"),IF(I5=2272,49.2849,IF(I5=2273,49.31,IF(I5=2274,49.2745,IF(I5=2460,31.39,IF(I5=2327,42.82,IF(G32="LIFELINE",IF(I5=2272,13.73,IF(I5=2273,13.73,IF(I5=2274,13.72,IF(I5=2327,12.05,IF(G32="COURTESY",IF(I5=2272,"FREE",IF(I5=2273,"FREE",IF(I5=2274,"FREE",IF(I5=2327,"FREE"))))))))))))))))

Well, without setting up a spreadsheet to check all of your conditions, I can see right away that your AND function has only one condition, so that may be part of your problem. Typically an AND function checks to see if at least 2 things are TRUE, but you are only checking 1 item.

It won't give you an error if you only check one item, but I'm guessing that that's not what you want to do.

Maybe this would work better for you:

Set up your possible I5 values and the corresponding "results" in a table someplace else on your spreadsheet. e.g.

A B C D 1 2272 49.2849 13.73 Free 2 2273 49.31 etc. 3 2274 etc.Then use an IF and a VLOOKUP

=IF(G32= "basic", VLOOKUP(I5, $A$1$D$5, 2, 0), IF(G32="LIFELINE",VLOOKUP(I5, $A$1$D$5, 3, 0), etc.Each VLOOKUP will look up the value in I5 and pull the result from the desired column, based on what is in G32.

In my mind, that's easier to follow and if you ever need to modify the values you are searching for (I5) or the corresponding results, all you have to do is change the values in your data table.

Hi, I have made some comments based on your formula

1. If G32 = "Courtesy" then the result is "Free", irrespective of the value in I5, despite the fact that only 4 values are used in the "Courtesy" part of the formula (2272,2273, 2274 & 2327)

2. In the "Lifeline" part of the formula only 4 of the 5 values in I5 are used: 2272, 2273, 2274, & 2327

3. "In the "Basic" part of the formula there are 5 options available: 2272, 2273, 2274, 2460 & 2327

4. There is no outcome specified if I5 does not contain one of these numbers.

5. There is no outcome specified if G32 does not contain one of the three words: "Basic", "Lifeline" or "Courtesy"I have constructed the formula based on the assumptions -

Four possible outcomes for "Lifeline"

Five possible outcomes for "Basic"

and one outcome ("Free") for "Courtesy"If G32 does not contain one of the recognized words, the formula returns "Error"

If for "Basic" and "Courtesy" I5 does not contain one of the 4 or 5 specified values respectively, the formula returns "Error"I have split the formula into parts for ease of viewing - recontruct a single string before pasting it into the formula bar of the appropriate cell.

=IF(G32="Basic",IF(I5=2272,49.2849,IF(I5=2273,49.31,IF(I5=2274,49.2745, IF(I5=2460,31.39,IF(I5=2327,42.82,"Error"))))), IF(G32="Lifeline",IF(I5=2272,13.73,IF(I5=2273,13.73, IF(I5=2274,13.72,IF(I5=2327,12.05,"Error")))), IF(G3="Courtesy","Free","Error")))Hope this works, as there was not a lot to work with!

Regards

Thank you very much,

your comments were correct...there is no 2460 lifeline code. I entered the formula as written and it worked fine thank you very much for your help I have been stuck on this for 2 days.

Did you consider using the VLOOKUP method I suggested? It's much shorter and certainly much easier to maintain than the Nested IF.

I havent tried the Vlook up yet the other formula was so close to the one I had it was easy to transpose. I do however intend to ty the vlookup to see if it is easier to change the data becasue that is something I do frequently. These codes are customer costs and of course prices change regularly so it would be nice to go to the table and just make the change there. I will give it a try..I havent used vlookup before so it will be a learning experience.

After I posted my suggestion I actually set up a spreadsheet using a table with your values and the VLOOKUP method. It worked out fine, and as we noted, changes to the data are much easier than changing the formula.

You also don't have to worry about entries that don't have results (the "Error" results) since you can just have blank cells where you don't have data. If those combinations will never show up in a cell, you don't have to worry about them.

You could even take it to the next level and not even hardcode the "Basic", "Lifeline", etc. into the formula.

If you use those values as column headings in the data table, you could use the MATCH function to determine the Column for the VLOOKUP function. That way, if those text values ever needed to be changed, you could do it in the table and never have to touch the formulae.

If you need any help with setting this up, let us know.

Hi, From what you say, using a lookup table approach with VLOOKUP() will be much easier to maintain in the longer term.

It will definitely be worth the effort to get it working.

Regards

Thanks DerbyDad

I would like to try to set up the data tables. Perhaps you could email me off forum I can show you the spreadhseet?

Thanks

Tony

edited by moderator: Removed email address

I have removed the email address you posted in you latest response. You should never post your email address in a public forum.

Send me an email address via PM and I'll send you an example of the VLOOKUP method - but it might not be today.

Just click on my username above this post and choose the PM option.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History