0

using an Array formula

Jet Reports Historic Posts

February 10, 2011 08:33

None

Follow

Hi ,

Have some trouble using an Array Formula, (index and match, to performe a lookup based on two columns)

I can only run the report correctly If I run the report in designer mode, and every time I have to re- enter (control-shift-enter) this formula to run the report correctly,
then I save the report, rerun it from viewer mode, gives me a value error

any advise is appriciated

• Facebook
• Twitter
• LinkedIn

8 comments

• 0
  
  Jet Reports Historic Posts February 10, 2011 12:46

  Hi Jan,
  
  If you can attach the report with maybe a screenshot of what you want to do, it will help us to understand better what you're trying to achieve.
  
  Thx.

• 0
  
  Jet Reports Historic Posts February 10, 2011 14:05

  I hope this attahcemnt and sample file will work
  
  
  in J10, I want to have the buyer,
  
  lookup buyer (match Dim 2 + supplier name >> returns buyer)
  
  cannot use vlookup, because of the 2 column match)
  now i''am using index and Match, which works in designer mode

  • testcase array formula.xlsx
• 0
  
  Jet Reports Historic Posts February 13, 2011 20:30

  Hi Jan,
  
  When I adjust the formula in your report a bit I get the correct result. Do not forget to press Ctrl+Shift+Enter to create an array formula:
  
  =INDEX(buyer!$E$13:$G$16;MATCH($C10&$I10;buyer!$E$13:$E$16&buyer!$F$13:$F$16;0);3)
  I cannot check this by running the report as I don't have your data. Please let me know if it works now.

• 0
  
  Jet Reports Historic Posts February 14, 2011 13:41

  Hans,
  
  Thanks,
  
  I noticed the differences, i'm afraid it where tyop's from my side. :oops: :oops:
  
  In designer mode
  I have entered and confirmed with Ctrl+Shift+Enter , and run the report, and it works, the result wanted
  
  Then I saved it, still looking good,
  closed sheet
  re opend the sheet and run the report in viewer mode, Now I get #value! (#WAARDE!)
  
  the formula has been copied to the other rows, but without the Ctrl+Shift+Enter
  in the formula bar =INDEX(INKOPER!$E$13:$G$414;VERGELIJKEN($D10&$N10;INKOPER!$E$13:$E$414&INKOPER!$F$13:$F$414;0);3)
  while in my opinion it to should be
  {=INDEX(INKOPER!$E$13:$G$414;VERGELIJKEN($D10&$N10;INKOPER!$E$13:$E$414&INKOPER!$F$13:$F$414;0);3)}
  It's confusing me, while I believe all is now correctly entered because it does run in designer mode perfectly..

• 0
  
  Jet Reports Historic Posts February 14, 2011 16:06

  I'm sorry Jan, but I have no idea. I'm running Jet Reports 2010 and it is just perfect.
  
  Portland, we have a problem. Can you help please?

• 0
  
  Jet Reports Historic Posts February 14, 2011 19:40

  Hi Jan and Hans,
  
  I'm afraid Excel array formulas (those created with CTRL+SHIFT+ENTER, not to be confused with the Excel arrays returned by some Jet formulas) don't work with a Jet Reports workbook with +VALUES or +LOCK if they are placed on a worksheet that has Jet formulas. The behavior will be that the first report run will work, but on subsequent runs it will not work because Jet Reports will copy the formulas from the formula sheet back to the report sheet and the CTRL+SHIFT+ENTER part of the formula (what makes it an array formula) will not happen as you have noticed.
  
  We have tried to address this issue in the past, but I'm afraid it just isn't possible. There are several reasons for this, but as I remember it I believe the most important problem is that the Excel API does not allow us to easily detect or recreate this type of formula.
  
  Basically, you cannot use Excel array formulas on sheets with Jet functions. You should be able to put Excel array formulas on sheets that do not contain Jet functions because Jet formula sheets will not be created for those sheets. Does that answer the question?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts February 14, 2011 20:21

  Ok, your asnwer is clear, I want something which cannot be done this way. alhought it was a nice solution.
  
  Any idea?
  now thinking about something like
  
  CONCATENATE within a VLOOKUP , and extra olumn in Table_array for the CONCATENATE placing the two fields DIM and SUPPLIER in one string
  
  Now I have only one match to look for, VLOOKUP would be the easiest way?
  
  VLOOKUP(CONCATENATE($C10;$I10);buyer!E12:H16;4;1)
  Any pitfall?

  • sample CONCATENATE_VLOOKUP.xlsx
• 0
  
  Jet Reports Historic Posts February 14, 2011 20:22

  That's too bad, Hughes. I learnt to like the INDEX() and MATCH() a lot.
  
  But maybe, Jan, you can use the concept of these functions in your report. What I did in the enclosed example is combine the 2 search ranges into 1 single range. And then the good old VLOOKUP() works again :)
  
  Please let us know if it works, ok? And I guess I need to rewrite my blog :(

  • This could work.xlsx