none
search by row and column simultaneously RRS feed

  • Question


  • How could i complete the cells in the table above with the data in the table below ("val" field)

    NUM    90R 90E 90C 90S
    1043
    1027
    1193
    1260
    1188
    1167
    900


    num cc val
    1260 90R 1,47
    900 90E 3,28
    47 90C 3,34
    1027 90C 8,53
    84 90S 23,15
    108 90E 44,47
    1167 90R 12,83
    113 90R 3,17
    127 90E 26,3
    1188 90R 1,63
    137 90R 10,39
    137 90S 0,71
    152 90E 16,69
    220 90E 13,44
    1260 90E 5,86
    277 90E 2,99
    1260 90E 8,85
    900 90C 2,72
    1193 90R 14,83
    1167 90S 12,93
    371 90R 8,69
    380 90E 5,61
    1193 90C 13,87
    382 90R 0,55

    thanks in advance

    Wednesday, July 24, 2019 9:19 AM

Answers

  • Hi,

    Based on your sample the value you want to return is number and there are no duplicate combination. I suggest you try to use SUMPRODUCT function the get the result:

    =SUMPRODUCT(($A$14:$A$37=$A2)*($B$14:$B$37=B$1),$C$14:$C$37)

    Hope it's helpful.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, July 25, 2019 7:36 AM
    Moderator

All replies

  • To:  rafa_c
    Re:  how?

    You could start by sorting them together...

    NUM     CC    VAL
    47      90C    3,34
    84      90S    23,15
    108      90E    44,47
    113      90R    3,17
    127      90E    26,3
    137      90S    0,71
    137      90R    10,39
    152      90E    16,69
    220      90E    13,44
    277      90E    2,99
    371      90R    8,69
    380      90E    5,61
    382      90R    0,55
    900      90C    2,72
    900      90E    3,28
    900         
    1027      90C    8,53
    1027         
    1043         
    1167      90R    12,83
    1167      90S    12,93
    1167         
    1188      90R    1,63
    1188         
    1193      90C    13,87
    1193      90R    14,83
    1193         
    1260      90R    1,47
    1260      90E    5,86
    1260      90E    8,85
    1260        
    Wednesday, July 24, 2019 1:14 PM
  • Hi,

        You could use =SumIfs(), which is happy to sum single values and act as a lookup, or =INDEX(Range,MATCH(),MATCH(()).

    Ethan


    Ethan Strauss

    Wednesday, July 24, 2019 9:52 PM
  • Hi,

    Based on your sample the value you want to return is number and there are no duplicate combination. I suggest you try to use SUMPRODUCT function the get the result:

    =SUMPRODUCT(($A$14:$A$37=$A2)*($B$14:$B$37=B$1),$C$14:$C$37)

    Hope it's helpful.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, July 25, 2019 7:36 AM
    Moderator
  • Hi Emy.

    It works fine and and allows simultaneous search by rows and columns.

    I've tried what Ethan suggest INDEX(Range,MATCH(),MATCH(()), but i haven't got it to work for my modelThanks!!

    Monday, July 29, 2019 5:51 PM
  • Hi Ethan, thanks for your reply.

    I tried to follow your indicacion using index and match, but without getting the result I'm looking for. Emi solution match my question.

    Thanks for your time and help

    regards

    Monday, July 29, 2019 5:59 PM
  • Hi!

    Thanks for reply, but in your answer you use several rows for each "num" that have more than one value, but I need only one for the results table.

    thanks for your interest in helping.

    regards

    Monday, July 29, 2019 6:11 PM