Vlookup Returns #N/A in XCelsius

[Former Member]

I just acquired XCelsius 2008 and have run into an issue that I am hoping someone can help with. I am building a model and am finding that when I use a Vlookup of a table of data, the data is found and displays correctly in the Excel window of XCelsius and also in the display window of XCelsius. However, when I run a Preview in Flash the data in the Vlookup area shows as #N/A.

I really need the Vlookup option to work as I have a number of items to lookup which would exceed the nested IF function. Can someone point me in the right direction please...?

Thanks, David

[Former Member · Accepted solution]

Your problem may be that you are not listing all of the parameters for the VLOOKUP function. To use functions in Xcelsius, you may need to list all parameters in a function (even the ones with default values).

[Former Member · Accepted solution]

Owen and John,

Thank you for the suggestions.

Unfortunately, neither one fixed this issue in Xcelsius.

This is really frustrating as VLookup routines and naming ranges have been a staple of Excel for a long time. I don't get what is happening in the conversion to Flash that is not picking up what is on the canvas that displays correctly.

David

[Former Member · Accepted solution]

The new formulas referenced above that were inserted per Owen's and John's suggestions were...

=INDEX(F5:I8,MATCH($B$3,F5:F8),3)

=VLOOKUP($B$3,F5:I8,3,TRUE)

These still yield " #N/A" results in the cells in the Flash Preview despite showing correctly on the canvas.

David

[Former Member · Accepted solution]

Examples above seem to work for me David...

Heres my xlf..

http://www.mediafire.com/?m2nv2ipxtuv

I get the NA error if i try to embed these sort of formula in IF statements etc... but thats a bug with 2008.

[Former Member · Accepted solution]

Now I am really puzzled as either both should work or neither. The last post displays the two types of look-up while mine does not. Does anyone have any insights into what is needed to resolve this or if this bug is getting fixed...?

Thanks, David

[Former Member · Accepted solution]

David, can you post your file? (I don't know how to do it on these forums, but you could try the site Jez used).

[Former Member · Accepted solution]

Good thought Owen...

Here is the sample file link...

http://www.mediafire.com/?xid2wt9buyy

Thanks, David

[Former Member · Accepted solution]

wish someone would turn attachements on on these forums. mediafile seems a decent, free, no logon required, type site...

[Former Member · Accepted solution]

simple solution mate.

edit your "list box 1 and from the general tab change the insertion type from "position" to "label"

works for me

[Former Member · Accepted solution]

basically you're writing either 1, 2 or 3 into the B3 cell rather than A, B or C which your lookup is expecting... hence its returning you NA because it, correctly, cant find a match.

[Former Member · Accepted solution]

Jez,

Thank you very much...!

The conversion from position to label in the menu activated the calculation lookup correctly in the INDEX | MATCH model.

The VLOOKUP model still kicks back a " #N/A " in the Flash, while displaying correctly in the canvas. Any thoughts on how to be able use VLOOKUP and HLOOKUP...?

David

[Former Member · Accepted solution]

hmmm yea... The vlookup is a bit weird

I can get it to work by looking for some text such as "UUU"... but if i put in "A", "B", "C" i get the NA error

Give it a go yourself... in your sheet change the "C" in H5 to "UUU" and the "C" in E8 to "UUU" then add an option of "UUU" into ListBox1.

For me when i select UUU in the listbox i get 15 30 45, i.e. the c values!

Other letters like "hello" seem ok, though "DAVID" doesn't!

To be honest I'm either missing something silly or this is a very weird bug to say the least :D:D:D

Jez.

[Former Member · Accepted solution]

And whats weirder is that I can't reproduce it in my little example either!

[Former Member · Accepted solution]

I am having the same problem with VLOOKUP. I am not passing any 'Position' values to destination cells used by the vlookup formula. Can you explain more precisely why the problem occurs with vlookup? Is it a bug with Xcelsius 2008?

[Former Member · Accepted solution]

Another important thing about vlookup:

If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

(this is a quote from Excel help)

I looked at David's file, and he searches in the table that looks approximately like this:

Table A

A 5

B 10

C 15

VLOOKUP fails, because the first column is not sorted ("Table" should not participate or should be the last row). If I exclude the first row (header) from the search range, VLOOKUP works fine. If I specify the last parameter (range_lookup) as FALSE, it also works fine.

[Former Member · Accepted solution]

Hallelujah! Thanks, it works now. I had the range including the header, like in your example, so the sort didn't work, therefore throwing off the vlookup. Such a relief.

[Former Member]

David,

VLOOKUP is a supported function in Xcelsius 2008. Can you post your Xcelsius file or an example file using the same excel formulas?

Are you by any chance using an INDIRECT function anywhere in your Excel logic? If so, that could be the problem because INDIRECT is not supported. Otherwise, my guess would be some problem with the way the VLOOKUP or its precedent functions are set up.

[Former Member]

Owen,

Thank you for the respone. I have created a simple example to represent what I am seeing, both with VLookup and with Index | Match.

How do I upload the XCelsius file to the forum...?

David

[Former Member]

David,

Are you using dynamic references for your column index in your Vlookups by any chance? Same for index / match?

Can you cut / paste the formula?

Hope this helps!

Xcelsius Gurus

_______________________

http://www.everythingxcelsius.com

"Xcelsius Gurus share templates, tips, reviews, tricks and much more!"

[Former Member]

Here are the two formula samples that I have tried. Both of these display in the data correctly including any calculations based on those lookups in the canvas view. However each of these formulas results in " #N/A " when converted to Flash in the Preview view.

=VLOOKUP($B$3,UOM,3)

=INDEX(UOM,MATCH($B$3,UOM_List,0),3)

David

[Former Member]

Ah, your problem is that you are using named ranges. I don't think Xcelsius supports named ranges. I bet if you convert those to actual cell references it will work.

[Former Member]

I have several Vlookups and Hlookups using named files so I can afirm they work. I had a similar problem unitl I went in and changed the number format. I actually had to assign a format for each value rather than using the format passed from the Excel file.

[Former Member]

I have changed the format of the vlookup results-cells in Excel, as shown in Xcelsius, to Number format from General. My Pie Chart sourcing its data from these cells is still not reading the distinct number values in Preview mode. It defaults to zero in the pie chart for all values instead. Comments???

[Former Member]

Hi,

my advice is to always use "Source data" binding and take the values you are inserting from some Excel cells; don't just type them in the property sheet. I have seen many times that lookup functions fail because control inserts value of different type than what you are searching for, and comparison fails. For example, if you campare string "1" to number 1, they are not equal. When you just display them in a grid, for example, you don't see the difference, so you think they are equal and expect lookup function to find match, but it does not because they are of different type.

Hope it helps.

Margaret