IF Function not returning as expected

kcalta

Hello

I have an IF function I am trying to run that evaluates a cell, and then returns one of two indexing formula results

I have verified that each indexing function and the If function itself with different results all seem to work.

But when I plug them together, my IF function stops evaluating "3100" properly, or it will pull up the Zip result for everyone rather than LOB result for some depending on what PRIN@row says.

As background, PRIN is a dropdown column with four options, one of which I need LOB codes for, two of which I need Zips codes for and one of which is NA so I figured that targeting the most specific one would work best for the IF function.

Thanks for any help in advance!

Paul Newcome

You have too many parenthesis spread throughout the formula. If you can copy/paste it here, I would be happy to help clean them up.

kcalta

Here is plaintext

=IF(CONTAINS(3100, PRIN@row), (INDEX({ZIPS ALL}, (MATCH([Job Type]@row, {ZIPS LOB}, 0)), 4)), (INDEX({ZIPS ALL}, (MATCH(ZIP@row, {ZIPS ZIP}, 0)), 4)))

I did notice many examples did not put nested formulas in parentheses and many did. Is there a specific rule that I am missing for when this is needed?

Thanks!

Paul Newcome

For the parenthesis... I try to use as few as absolutely possible simply because they can get out of control very quickly. The thing to remember is every open parenthesis needs a closed parenthesis. Every single FUNCTION will have an open and closed. Other than that you only really need to worry about them when a certain order is needed but not necessarily specified such as running math equations (follows P.E.M.D.A.S.).

=IF(CONTAINS("3100", PRIN@row), INDEX({ZIPS ALL}, MATCH([Job Type]@row, {ZIPS LOB}, 0), 4), INDEX({ZIPS ALL}, MATCH(ZIP@row, {ZIPS ZIP}, 0), 4))

I also notice that it looks like you are referencing column 4 in your INDEX function.

INDEX({range to pull from}, row number, column number)

In the above, the MATCH provides the dynamic row number. Is there a reason your INDEX range is covering so many columns? You should be able to only select a single column that you want to pull from. It is very different from VLOOKUP in that (and a number of other) way(s).

kcalta

Thank you! I agree, parentheses can get out of control very quickly. I have stared and adjusted this one so long I am sure there were one or two runaways.

As far as why so many, I need to basically match a Job number in another datasheet to the cell/row, then the Prin within the data sheet I am working in, then either the zip or job type in a third helper sheet that consolidates all of the info of who is assigned to what, to return the scheduler.

It's a lot and not how I would prefer to set things up, but I am working within the confines of an existing system.

Paul Newcome

I understand needing to look at different sheets, but what I mean is each of the individual INDEX functions.

INDEX({ZIPS ALL}, MATCH([Job Type]@row, {ZIPS LOB}, 0), 4)

That first range really only needs to be the column you are pulling from. Then you wouldn't need the 4 there at the end. It doesn't make much difference as far as functionality goes, but if the sheet is really busy with a lot of formulas/cross sheet references, the fewer cells you can reference the better performance you will have on the back-end. It also allows you to rearrange the source sheet without having to worry about messing up a formula since you are tracking a specific column as opposed to a column number.

kcalta

As I see, I need that because I need one index to reflect if true and one to reflect if false.

I tried plugging in the cleaned up formula and I still end up with the same issue. The "If" statement doesn't seem to be functioning properly. It still just appears to be matching from the zip code column of my reference sheet, regardless of the PRIN number listed as the value in the if function.

Paul Newcome

You have both INDEX functions pulling from the same column. The only difference between the two is the first is matching on [Job Type], and the second is matching on ZIP. The MATCH range does not have to be included in the INDEX range. What I am saying is you can takes that {ZIPS ALL} range and make it a single column (the one you want to pull from) instead of multiple columns which will make the overall setup more flexible and less prone to break as well as more efficient on the back-end.

As for the IF statement... Are you able to provide a screenshot where you are expecting the CONTAINS to be true which would cause it to match on the Job Type?

kcalta

Edit: I rearranged my data sheet and have updated screenshots as well as the sheet I am trying to pull data into

Hi, Here is a screenshot of the reference data sheet:

And this is the sheet I am pulling into with the formula you fixed for me plugged in

Now it is saying no match since I rearranged and re-established my formula references

Before, it was pulling info based on zip code regardless of prin. Anything in 3100 should be pulling by LOB.

I hope that makes more sense.

Thanks again for your help!