Solved

# Revise Excel INDEX formula

Posted on 2013-06-19
Medium Priority
341 Views
Below is the formula that we written prior.  It’s not evaluating the logic correctly.  (I omitted a logical check with I requested it earlier.)

=IF(ISNA(IF(COUNTIF('Selected VDOTs'!\$B\$2:\$B\$571,\$A193)=1,IF(VLOOKUP(A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)=0,"In Flight",VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)),IF(COUNTIF('Selected VDOTs'!\$B\$2:\$B\$571,A193)=COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$571,\$A193,'Selected VDOTs'!\$G\$2:\$G\$571,VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)),IF(VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)=0,"In Flight",VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)),IF(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$571,\$A193,\$G\$2:\$G\$571,0)>0,"In Flight","Changes required")))),"",(IF(COUNTIF('Selected VDOTs'!\$B\$2:\$B\$571,\$A193)=1,IF(VLOOKUP(A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)=0,"In Flight",VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)),IF(COUNTIF('Selected VDOTs'!\$B\$2:\$B\$571,A193)=COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$571,\$A193,'Selected VDOTs'!\$G\$2:\$G\$571,VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)),IF(VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)=0,"In Flight",VLOOKUP(\$A193,'Selected VDOTs'!\$B\$2:\$M\$571,12,0)),IF(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$571,\$A193,\$G\$2:\$G\$571,0)>0,"In Flight","Changes required")))))

Overview:
Two tabs in the spreadsheet are related to this formula: The Key Control tab where the formula will be stored and the “Selected VDOTs” tab where data will be evaluated.

The formula will evaluate the value in Column A (Control ID) on the Key Control tab against the related rows for Column A’s value on the Selected VDOTs tab.

The columns on the Selected VDOTs tab that must be evaluated are: “Col B-stores the value from Column A (Control ID) [There will be multiple instances of this value because more than one test id may be linked to a Control iD.]” and “Column M – stores the test result values.]

[When the formula above was first proposed, I thought the test Id was relative.  I no longer think column G (Test ID name) is relative for this evaluation.]

The formula must perform the below logic:
If the value in “Control ID/Column A” on the Key Control tab is not found on the Selected VDOTs tab, return a blank value.
If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” value contains blanks, return a blank value.
If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” values match, then return that value.
If any of the rows in Selected VDOT/Column M for any given “Control ID/Column A” values contain “In Flight”, then return “In Flight”.
If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” values contain “Validated” or “Changes Required”, then return “Changes Required”.

The file is attached...
Key-Control-Tracker-061613-v3.xls
0
Question by:Feisty472
• 7
• 4

LVL 14

Expert Comment

ID: 39261982
Hello Feisty472,
With all due respect, you will never see the day that this workbook serves you. Instead, you will live to serve this workbook. Every minute spent on perpetuating its life is a minute wasted.
Let me help you find a VBA replacement for the dysfunctional formula and put you on a path to replace most formulas in this workbook with code, if not all.
0

LVL 81

Expert Comment

ID: 39262343
Rather than wrapping half of your ginormous formula inside ISNA, why not use IFERROR to return the blank?

Your stated logic fails to cover the possibility that column M contains differing values that aren't all Validated or Changes Required. I decided to return "Review" in such situations.

The resulting formula is still so large you will be challenged to maintain it, but...
=IFERROR(IF(COUNTIF('Selected VDOTs'!\$B\$2:\$B\$543,\$A3)=COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,VLOOKUP(\$A3,'Selected VDOTs'!\$B\$2:\$M\$543,12,FALSE)),VLOOKUP(\$A3,'Selected VDOTs'!\$B\$2:\$M\$543,12,FALSE) & "",IF(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,"In Flight"),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,{"Validated","Changes Required"}))=COUNTIF('Selected VDOTs'!\$B\$2:\$B\$543,\$A3),"Changes Required","Review"))),"")
0

LVL 81

Expert Comment

ID: 39262479
I probably should have mentioned that my suggested formula does not need to be array-entered.

And if you are willing to add some auxiliary columns for the results of the COUNTIF and VLOOKUP (perhaps to columns W and X), then you could simplify the formula to:
=IFERROR(IF(W3=COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,X3),X3 & "",IF(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,"In Flight"),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,{"Validated","Changes Required"}))=W3,"Changes Required","Review"))),"")
0

Author Comment

ID: 39268565
Hey  Byundt,

First of all, thanks for the offer to write a VBA replacement.  The spreadsheet is actually only a temporary situation.  We have developers working on a permanent solution.  I will keep you in mind if that falls through!

Secondly, i love that fact that you were able to *almost* achieve the needed results without an Index.  Yay you!!

=IFERROR(IF(COUNTIF('Selected VDOTs'!\$B\$2:\$B\$543,\$A3)=COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,VLOOKUP(\$A3,'Selected VDOTs'!\$B\$2:\$M\$543,12,FALSE)),VLOOKUP(\$A3,'Selected VDOTs'!\$B\$2:\$M\$543,12,FALSE) & "",IF(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,"In Flight"),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,{"Validated","Changes Required"}))=COUNTIF('Selected VDOTs'!\$B\$2:\$B\$543,\$A3),"Changes Required","Review"))),"")

Three things:

1. [Byundt] Your stated logic fails to cover the possibility that column M contains differing values that aren't all Validated or Changes Required. I decided to return "Review" in such situations.
[Feisty472] The column values will only be <Blank>, In-Flight, Changes Required or Validated.  This should be no need to return Review if the logic holds.

2.  A requirement was overlooked: If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” value contains blanks, return a blank value. [Feisty472]  Currently, it will return "Review".  Please just simply return a blank.

3.  I did forget a piece of logic:  If any of the rows in Selected VDOT/Column M for any given “Control ID/Column A” value contains any Status along with a blank row, return "In-Flight".  (To illustrate: If there are three rows, two contain "Validated" and one is still blank, return "In-Flight".  If there are nine rows, four contain Changes Required, three contain "Validated" and two are blank, return "In-Flight".)

Many, many thanks for you efficiency!
0

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 39268818
The column values will only be <Blank>, In-Flight, Changes Required or Validated.  This should be no need to return Review if the logic holds.
Column M also contains 210 cells that return #N/A error value. Consider the case when column B contains BKCY.PAY.1.2. On row 19 column M has "Changes Required", while on row 29 it has #N/A error value. The column M values don't match, so my formula returns "Review". I think it better to continue returning "Review" until your issues with #N/A are addressed.

I agree that my formula wasn't working if column M contained all blanks. I needed to concatenate an empty string in one more place.

Making also the change for your new condition, you might consider:
=IFERROR(IF(COUNTIF('Selected VDOTs'!\$B\$2:\$B\$543,\$A3)=COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,VLOOKUP(\$A3,'Selected VDOTs'!\$B\$2:\$M\$543,12,FALSE) & ""),VLOOKUP(\$A3,'Selected VDOTs'!\$B\$2:\$M\$543,12,FALSE) & "",IF(SUM(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,{"In Flight",""})),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!\$B\$2:\$B\$543,\$A3,'Selected VDOTs'!\$M\$2:\$M\$543,{"Validated","Changes Required"}))=COUNTIF('Selected VDOTs'!\$B\$2:\$B\$543,\$A3),"Changes Required","Review"))),"")
Key-Control-Tracker-061613-v3Q28.xlsx
0

Author Comment

ID: 39270106
Ahhhh...now i understand what you are talking about when you reference "#N/A".  When you send the formula, I change the actual column in which the formula will reference from M to L.  "M" is my working column that has the VLOOKUP in it.  Because of some manual checking i have to do, I check each week to see if any values changed from the week before.  If they have, I evaluate them as an audit of our new employee.  If the change is correct, I move the 'official' status to Column L.  Column L will never have "#N/A".  But, many thanks for the heads up.

I'm getting ready to build out another spreadsheet.  If I run into stumbling blocks on creating the formula, like I did with this one, how can I work with you?  (Some of the other guys weren't as good or quick to respond as you.)
0

Author Comment

ID: 39270112
I've requested that this question be closed as follows:

Accepted answer: 0 points for Feisty472's comment #a39270106

for the following reason:

This expert was not only efficient in the formulas he created, but he is also extremely timely and patient.  I would love to work with him exclusively!!  :-)
0

Author Comment

ID: 39270108
Many thanks!
0

Author Comment

ID: 39270115
I don't understand what this "Close Request Pending" is all about.  I must have clicked the wrong button.  I wanted to Grade and Accept the solution.
0

Author Comment

ID: 39270142
I've requested that this question be closed as follows:

Accepted answer: 0 points for Feisty472's comment #a39270115

for the following reason:

Ok..If I have to type how much I loved this guy, I am going to lose it!!

I understand that I couldn't simply use a COUNTIFS to evaluate the conditions.  I needed to use deeply embedded formulas.  My expert helped me understand this..
0

LVL 81

Expert Comment

ID: 39270135
If you have trouble implementing the solution from this thread in your real workbook (or in your next workbook), please continue to post in this thread. Doing so will increase its value in the Experts Exchange database--and you should find that all of the regulars in the Excel Zone are quite willing to work with you in that fashion.

While I'm flattered that you would like to work with me exclusively in the future, site rules discourage that practice. Rest assured, however, if I see a new question from you, I would be pleased to try to help you again.

0

Author Comment

ID: 39272379
Ohhh..I see now.  I guess it's not as intuitive as I thought.  I will correct that now.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.