SOLVED

Formula not working - is it because we are using an older Excel version?

Copper Contributor

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

12 Replies

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!

 

@PReagan 

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

 

 

@JanePG 

 

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:

  1. What's the nature of the data in cells E5:E8?
  2. 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)?
  3. 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.

@mathetes 

 

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

@JanePG 

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

best response confirmed by JanePG (Copper Contributor)
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. 

@mtarler 

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.

@JanePG 

 

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

 

@mtarler 

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. 

@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)

@PReagan 

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

1 best response

Accepted Solutions
best response confirmed by JanePG (Copper Contributor)
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. 

View solution in original post