Forum Discussion

JanePG's avatar
JanePG
Copper Contributor
Jul 16, 2020
Solved

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(VLOOK...
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jul 17, 2020

    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. 

PReagan's avatar
PReagan
Bronze Contributor
Jul 16, 2020

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!

 

  • safikul6190's avatar
    safikul6190
    Copper Contributor
    Dec 18, 2020

    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

  • JanePG's avatar
    JanePG
    Copper Contributor
    Jul 17, 2020

    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

     

     

Resources