I am trying to automate some checks in a .csv file with multiple sheets. I need a formula which will search a specific sheet (Medication sheet) for two column headers (AgeAtStart and AgeAtStop). Once found, it will perform an IF statement (=IF(E2<=F2,"Ok","Not Ok")) on the values of those two columns, making sure that AgeAtStart is less than or equal to AgeAtStop.

Medication Sheet

  AgeAtStart    Other Column    Other Column    Other Column    AgeAtStop
    61.85           x                 x               x           61.98
    61.98           x                 x               x           72.22
    72.62           x                 x               x           73.74
    72.27           x                 x               x           72.57
    73.82           x                 x               x           73.85
    61.85           x                 x               x           61.98

I'm flexible regarding how the Summary sheet will report this, but my initial thoughts are reporting the number of AgeAtStart which are greater than AgeAtStop or returning the position of the cells which contain the incorrect age. I do not want to return results for each IF statement, but rather return a count of those IF checks which returned "Ok" or "Not Ok".

I've tried to accomplish this using a combination of VLOOKUPs and INDEX/MATCH, but so far can't get the logic down. Maybe VBA would be a better solution?

EDIT: Using cybernetic.nomad's implementation works great.

=IF(INDEX('Sheet2'!2:2,,MATCH("AgeAtStart",'Sheet2'!$1:$1,0))<INDEX('Sheet2'!2:2,,MATCH("AgeAtStop",'Sheet2'!$1:$1,0)),"OK","Not OK")

As a follow-up, I am putting this formula in a "Summary" sheet and referencing the "data" sheet. Is there a way to apply this formula automatically "x" number of times in the "Summary" sheet, where "x" represents the number of rows in the "data" sheet? So, if my data sheet has 100 rows, in the Summary sheet, the formula will automatically populate 100 times, referencing each 100 rows.

| improve this question | | | | |
  • Where do you want to output the results of your IF checks? A new column? Another sheet? Another file? That might help readers determine whether a formula or a macro is the better option. – user2800 Oct 17 '18 at 18:31
  • @user2800, the results can go anywhere. I don't want to have to look at all the results of each IF statement though, but rather show a summary of the results, if that makes sense. I'll edit my post to clarify. – user948434 Oct 17 '18 at 18:32

Assuming your data is in A1:E7, insert the following formula in F2

=IF(INDEX(A5:E5,,MATCH("AgeAtStart",A$1:E$1,0))<INDEX(A5:E5,,MATCH("AgeAtStop",A$1:E$1,0)),"OK","Not OK")

And populate down

To have a summary, you can filter on "OK" or "Not OK", or do a COUNTIF

To ignore blanks use:

=IF(AND(NOT(ISBLANK(INDEX(A2:E2,,MATCH("AgeAtStart",A$1:E$1,0)))),NOT(ISBLANK(INDEX(A2:E2,,MATCH("AgeAtStop",A$1:E$1,0))))),IF(INDEX(A2:E2,,MATCH("AgeAtStart",A$1:E$1,0))<INDEX(A2:E2,,MATCH("AgeAtStop",A$1:E$1,0)),"OK","Not OK"),"")
| improve this answer | | | | |
  • thanks! The formula works perfectly! I had a follow-up question, see edit. – user948434 Oct 17 '18 at 19:32
  • Actually, is it possible to ignore blank cells with this formula? – user948434 Oct 17 '18 at 19:45

I was able to expand upon cybernetic.nomad's initial reply to achieve my goal. I've detailed my implementation below...

Summary sheet Pass: Counts all occurrences of "1" Fail: Counts all occurrences of "0" Total Records: Counts rows if the Lookup returns "Yes" Summary

Lookup sheet Rows: Returns "Yes" if column A cells are not empty (row total will change with new files) AgeAt: If INDEX-MATCH returns a blank, does nothing. Otherwise, compare ages in the columns AgeAtStart and AgeAtStop Lookup

The formula in the Lookup sheet is copied down columns more rows in A and B than exit in my data. This is because we receive these files every two weeks and some will have more or less rows. Formula

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy