I am trying to pull data from a sheet with the vlookup function. The cell I am looking up was populated with an NL function.
Cell D5 contains the data, H5=vlookup(D5, NLA!$A$2:$B$40,2,FALSE) #N/A value is returned. What am I doing wrong or doesn't the vlookup work on a cell populated with a NL function?
Date
Votes
7 comments
-
Jet Reports Historic Posts If you know that the value you are looking for should be in the lookup range, then it's a formatting issue. Be sure that Jet is returning the value in the same format you are using to look it up.
For instance, I have a field that can contain all alpha, all numeric, or a mix of both for the characters. If I'm doing a VLOOKUP using that field, I have to be sure that I am looking up a TEXT value (even if I'm looking for an all numeric turn out).
You may have to use a formula like TEXT or VALUE in your VLOOKUP to make the formats match. -
Jet Reports Historic Posts VALUE worked! I had tried TEXT before but not VALUE. Thanks!
-
Jet Reports Historic Posts Glad to be able to help :)
-
Tom Wimberly I have this same problem with a report. My problem is that the Jet Report does not see the value in the other Excel Worksheet (completely different worksheet) unless I press the F2 for edit on the cell in the other Non-Jet spreadsheet. Even when the format on the other non-Jet worksheet is General, it corrects itself in the Jet Report when F2 is pressed on the non-Jet worksheet.
I should add that the data in the non-Jet worksheet was pasted from another spreadsheet even though I don't know if this matters.
-
Harry Lewis Could you provide more details about what it is you are trying to do?
For example: Using a recent release of Jet Professional, I set up a list of values on The_Data worksheet and then referenced that list from My_Sheet using a VLOOKUP function on the same line as an NL(Rows) function:
when I run the report, I get the values from the list.
What is it that you are trying to do?
-
Tom Wimberly This is my referenced spreadsheet.
This is my jet report that is trying to find the values in the above spreadsheet. "303701" appears in the above spreadsheet but isn't found.
-
Heather Rowe It looks like you might be running into the same problem as the original poster of this topic. in your reference spreadsheet 303701 is formatted as a number, but Jet appears to have formatted your output as a Text value (just guessing based on the fact that the output is not right-justified as numbers often are). Try using the VALUE formula to convert the Text data into Numeric.
Something like: VLOOKUP(VALUE($F4),'[Keyed-Pasted Data.xlsx]Data'!$A$1:$M$11,1,FALSE)
Please sign in to leave a comment.