View original (French)

Sum of multiple VLOOKUPs in a single cell

tranquille0123 Posted messages 2 Status Member -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
Hello,

I want to sum several HLOOKUP or VLOOKUP in a single cell, instead of doing:
=HLOOKUP(...)+HLOOKUP(...)+HLOOKUP(...).......
Thank you

Configuration: Windows XP / Firefox 16.0

4 answers

  1.
    PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    You can perhaps use a SUMIF
    =SUMIF($A$6:$A$20;$A6;$B$6:$B$20)
    instead of VLOOKUP to find an element and take the value from a column
    we sum values that meet one or more criteria.
    1
  2. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
     
    Hello,

    it is indeed possible to perform sums of VLOOKUP() or HLOOKUP() in a single cell.
    But one must not forget that the result of this kind of function is in text format, even if you are returning numbers. To get a result in number format, you must wrap the lookup function with the VALUE() function.

    To sum them up, the syntax is therefore:
    =VALUE(HLOOKUP(...)) + VALUE(HLOOKUP(...)) + ... + VALUE(HLOOKUP(...))

    Best regards.
    --
    That guy wasn't too bright, as he was naive.
    0
  3. tranquille0123 Posted messages 2 Status Member
     
    Thank you very much for your prompt response,
    BUT I don't want to pull up the VLOOKUP table every time, I just want to do VLOOKUP( ...sum of several cells) once, in fact summing several No_index_col

    Thank you
    0
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Good evening everyone,

    without a file and a specific example of what you want, it's hard to answer you.
    Please upload the xls file (reduced to what is necessary and anonymized) on cjoint.com and paste the provided link here.

    eric

    --
    You will never respond to an unsolicited PM...
    Well, that's done.
    0