1

I am trying to build a formula that simulates a "loop" in excel. The spreadsheet pulls data from a database into four spreadsheets. The formula needs to iterate through the sheets, moving onto the next sheet if the value found is #N/A. The current set up uses index(match()) to find the value in the table on the sheet. To move between sheets, I am using choose() to drive an indirect() to change to the next sheet, but I'm not sure how to simulate the loop... the problem seems to be that I would need to refer the function back to itself and then break out of the loop.

I'm imagining the solution would be something like 

=IFNA(CHOOSE( [the next number], INDEX(Sheet[the next number]!A1:Z500, MATCH(G1, Sheet[the next number]!A1:Z1), MATCH(A250, Sheet[the next number]!A1:A500)

The sheet cannot use VBA.

Improve this question
7
  • Might I ask why the sheet cannot use VBA? Commented May 13, 2017 at 2:05
  • What are you trying to do? Do you wish to split data from a database into four spreadsheets? Or do you wish to retrieve data from the four and don't know in which sheet they might be found? In the latter case you might look for a formula to place in the fifth sheet, but in either case your question doesn't specify whether you have a single criterion for data selection of multiple ones. Commented May 13, 2017 at 2:17
  • Meanwhile, worksheet formulas which simulate loops are called "Array Formulas". You might like to google for the expression and read what you find by C Pearson or Contextures. That might give you ideas. Commented May 13, 2017 at 2:20
  • Diligar- the sheet needs to be used with other sheets that have VBA and I think it would create conflicts. Commented May 14, 2017 at 2:41
  • Variatus - apologies for failing to be specific, I am trying to retrieve data from four sheets but don't know which it would be in. Not sure I understand what you mean about criterion comment.. the function would would iterate to the next sheet if the index() encountered an error. I'm familiar with array functions.. I hadn't thought of using one in this context, but will give that some thought. Let me know if you have Any additional thoughts Commented May 14, 2017 at 2:50

2 Answers 2

Reset to default
1

Further to my comment: Iterative calculations could be used, similar to this setup -

  • A1: =INDEX(B1:B4,A2)*1

  • A2: IF(ISERR(A1),A2+1,A2)

  • B1:B4: lookup values

When Index in A1 encounters a letter in B1:B4 it throws an error (from *1)

When A2 sees an error in A1, it increments

Since A2 is the row value that A1 is indexing, A1 looks at the next row, which may or may not cause an error

When A1 is not an error, A2 stops incrementing. See the below image

A2 iterates until A1 is not an error

A similar setup could be used to iterate through the sheets looking for an error in the index formula using ISNA, I would have used that as the example, but I'm not 100% sure what your formula is doing (it has too many open brackets!)

Notes

To access the iterative formulae option, go File->Options->Formulas->Check Enable iterative calculation. Set the value of maximum iterations to 1 if you want to hit F9 and increment through the sheets one at a time manually, otherwise set it to the number of sheets you are looking at (4?). That way you will loop your helper cell 4 times, and it will stop incrementing on whatever occasion it does not detect an error.

Also, I like to wrap my circular reference formula, A2, in an extra IF(A3 = "rst",1,...) so that I can reset the value without retyping the formula

Iterative calculations are great as they allow you effectively to save data in Excel mid calculation, but they must be used with caution to avoid infinite loops and huge calculation times - I use VBA because it's safer for that

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

It would be simpler to split the formula in to 5 different formulas:
the first 4 do the lookups on the 4 sheets
then the 5th formula uses nested If (or IFS if you are on Office 365 Excel 2016) to select the lookup with a valid result from the 4 formulas.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.