0

VLOOKUP in a NL("Table")

Jet Reports Historic Posts

April 26, 2013 10:07

None

Follow

Hi

I'm trying to reference a VLOOKUP formula (referencing a pre-populated sheet of data in my spreadsheet) in my NL Table, but it is not working.

I can get it the VLOOKUPto work using NL Rows, but I would like to use a Pivot Table to report on the data (including the data returned by the VLOOKUP), and while I could reference the Pivot table to a generic range of the NL Rows (e.g B5:N2000) this would return blank cells, so I would prefer the Pivot table to refer to the exact data which would be returned from the NL Table function.

Is this possible?

Thanks
Jon

• Facebook
• Twitter
• LinkedIn

7 comments

• 1
  
  Jet Reports Historic Posts April 26, 2013 12:29

  Hi Jon,
  
  First run the table and make the lookup in Report view. The normal problem is that your reference will no be valid when returning to designer view. It seems jet is deleting the columns.
  Then make your lookup as you would like to see. But when you enter your range, use INDIRECT and make the range as text. like this: =VLOOKUP(D4,INDIRECT("Report!D:D",1),1,0)
  
  When you now go back to designer view, the range will not change in your lookup
  
  Re-run the report and it works.
  
  Good luck

• 1
  
  Jet Reports Historic Posts April 26, 2013 15:11

  Thank you for the speedy response Michel.
  
  I've done as you suggested, including the INDIRECT element within Report mode. I did this by unhiding the end column hidden when running the report, and inserting a column; the formula went into the new column.
  
  However, when I go back to Design mode, my new column disappears, but the end column of the report is copied to the next column over.
  
  I've copied the basic spreadsheet with my Report set up, and also the sheet that is being referenced in the VLOOKUP.
  The formula I was trying to insert in the Report mode, based on your suggestion, is:
  =VLOOKUP(TEXT(F12,"0000"),INDIRECT("'Account data'!$B:$H",1),6,FALSE)
  Would you be able to guide me a little more please?

  • JPW Test.xlsx
• 0
  
  Jet Reports Historic Posts April 26, 2013 15:43

  You need to actually add the VLOOKUP formula when you're creating the report in the Jet Table Builder. There is a button for this in the Jet Table Builder under the list of fields. Then you can make the correct references to the table columns and the formula will be part of the table itself. I will do this for you manually and attach the result. Does this help?
  
  Regards,
  Hughes

  • JPW Test 2.xlsx
• 0
  
  Jet Reports Historic Posts April 26, 2013 16:14

  Thanks Hughes, I hadn't realised I could do that. I'm guessing it's obvious I am very new to Jet Reports!
  
  However, when I run the report, a dialog box says

  Invalid Field 'Formula(VLOOKUP(TEXT([@[Segment2]],"0000",'Account data'!$B:$H,6,FALSE))'.

  
  Even if I try to use a very basic formula, I get an Invalid Field error. However, I can't find out the correct syntax for an NP(Formula) code in the help files, so I'm relying on your own syntax that you used in your example.
• 0
  
  Jet Reports Historic Posts April 26, 2013 16:54

  Hmmm, that is strange. The workbook I attached actually works fine for me. You aren't using Jet Express are you? Formulas aren't allowed in Jet Express. Or it could be because you are in a region where they use semicolons in formulas instead of commas or something like that. If none of this helps, you could always open a ticket on the Jet Reports Support site, and I'm sure someone could help you sort of it.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 29, 2013 08:12

  OK thank you for the tip - I'll see about raising a ticket.
  
  Don't believe I have to use semicolons (I'm in the UK), and I'm using Jet Essentials.
  
  I'll see if I can do it in another report.
  
  Thanks for your help in any case.
  
  Cheers
  Jon

• 0
  
  Estuardo E. León D October 12, 2018 16:00

  The response from Michael works fine for me.
  
  Thanks.
  Cheers.
  Estuardo León.