Jul 16 2020 03:13 AM - edited Jul 17 2020 11:46 AM
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
Jul 16 2020 08:18 AM
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])
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!
Jul 17 2020 10:24 AM
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
Jul 17 2020 10:35 AM
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:
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.
Jul 17 2020 11:53 AM
Hi Mat
thanks for your reply.
Attached is a simple version of the spreadsheet which hopefully will make things clearer.
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
Jul 17 2020 12:38 PM
In general
=SUMPRODUCT(--LEFT($D$9:$D$12))
shall work.
As variant
=SUMPRODUCT(INDEX($B$9:$B$11,MATCH($D$9:$D$12,$A$9:$A$11,0)))
but I'm not sure how it works on pre-DA Excel
Jul 17 2020 01:30 PM
Solution@Sergei Baklan @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.
Jul 17 2020 01:56 PM
First formula shall work on any Excel. It could be complicated to extract most left number, not only first character, but I'm not sure that's the case.
Jul 17 2020 02:02 PM - edited Jul 17 2020 02:03 PM
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
Jul 21 2020 01:50 AM - edited Jul 21 2020 02:02 AM
Many thanks for your response re DA (and the Excel file), I now know far more about them and yes, my husbands version doesn't have =filter. His company are on the semi-annual enterprise channel where as I work in a school and am on current channel (and now have just gone to 2007)
Thanks for the comments about the =left formula, he only wants it to sum the first digit and it will only ever be 1 digit
Many thanks once again.
Jul 21 2020 02:06 AM
Jul 21 2020 05:46 AM
@JanePG you're very welcome and by the way, I just finally got the upgrade to DA so check with your husband and see, his might have been updated or ready for an update (you can check in File -> Account -> Office Updates -> Update Now)
Dec 18 2020 02:10 AM
While using Excel 365, wherein during V lookup function, the col_index_num / table array details are not displayed as it used to be while using the previous version of Excel
Jul 17 2020 01:30 PM
Solution@Sergei Baklan @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.