Forum Discussion
Formula not working - is it because we are using an older Excel version?
My husband has asked me to solve an Excel question he has.
He wants to use a vlookup to convert a text choice (validation drop down) to a number and then sum the numbers
I gave him:
=SUM(VLOOKUP(E5:E8,jpg,2,FALSE))
Which works fine for me.
It doesn't work on his, even when I use CSE and even when I type it in myself (just to be certain!)
My other solution, assuming that the text choice starts with a number, does however work fine for him: =SUM(LEFT(E5:E8,1)*1)
He is on Excel version 1908 of Office 365 whilst I am on version 2006. Do you think this is causing the issue or is there something I'm overlooking?
Any advice gratefully accepted.
Thanks Jane
SergeiBaklan JanePG Sergei, it would appear your formula also doesn't work for pre-DA and that is your answer Jane that it appears you have dynamic arrays and your husband does not. I suspect if you type =FILTER you will see information about it while your husband's version would only show FILTERXML as a valid function.
They are still rolling DA out so it is still possible your husband may get it soon.
As for your formulas please note the your "LEFT(... , 1)" is purely summing the first character and if those values exceed 1 digit OR if the corresponding values in the table change (e.g. 2today has "5" in col B) that formula will not work.
12 Replies
There may be some others here who can discern from what you've written what's going on.
I would need to ask some more questions:
- What's the nature of the data in cells E5:E8?
- What is the jpg to which your VLOOKUP formula refers? Is that a named range? A range of photo names (which, with their "jpg" file suffix, could explain the former)?
- Where is the validation drop-down in connection with all of this? How, asked another way, is it connected to the VLOOKUP?
It would actually help a lot if you could post a copy of (or a semblance of) your actual file, provided it contains no private or confidential info.
Finally, it would appear you both have older copies of Excel. In general, you'd probably both appreciate the most recent update. It has a number of really neat features that are totally new.
Hi Mat
thanks for your reply.
Attached is a simple version of the spreadsheet which hopefully will make things clearer.
- jpg are my initials and so I have named my lookup table jpg. Nothing to do with images.
- the data that I want to lookup up is chosen from a validated list, thus ensuring it will be in the lookup table, so in the cells E5:E8 they are alphanumeric but always start with a number
I'm afraid both of us are reliant on our respective companies for upgrades so we basically have what we are given. My husband may be able to get an upgrade if he can say that the version he is on is insufficient.
thanks
Jane PG
Maybe these non-array formulas--I did each of them "the old fashioned way"--which should work on your husband's computer, can also illustrate the difference between the capabilities.
These newest functions that have been referred to, in the most recent release, would give both of you a LOT more capabilities. Here's a link to a YouTube video that I used to become familiar with FILTER, UNIQUE, and SORT. I've been employing these functions ever since.
Maybe both of you could use it to sell your organizations on the value of the newer system. https://www.youtube.com/watch?v=9I9DtFOVPIg
Hello JanePG,
Without seeing the data that you are working with, it would be difficult to fully diagnose your problem. As a note, the VLOOKUP() function has the arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Your function takes E5:E8 as the lookup_value. Did you intend for this to be the table_array?
- The table_array value is jpg. Is this meant to be the lookup_value? As a note, if the lookup_value is a text value then it should be surrounded by quotes (""). For example, "jpg".
- The col_index_num is 2 but there are not 2 columns in the referenced table_array.
Please review these notes and provide a little more additional information regarding the results you intend to receive and I would be more than happy to help solve your problem!
Thanks for your response
The formula is working exactly as I want it on my computer, I haven't swapped things around accidentally so E5:E8 was an array I wanted to look up NOT the lookup table itself, that's called jpg
I've attached the spreadsheet itself, I'd be grateful if you could have a look. As I say, I think it's because my husband is using an older version of Excel and am just trying to confirm it and if possible then solve it for his version.
thanks
Jane
