Forum Discussion

KC_86's avatar
KC_86
Copper Contributor
May 17, 2019
Solved

LOOKUP Expression?

Hello,    I have two sheets in an Excel document, Data 1 and Data 2. Data 1 has Columns [Participant] and [Area]. Data 2 has the same columns but the [Area] is empty.    I would like to know ho...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    May 17, 2019

    KC_86 , it could be

    =INDEX('Data 1'!B:B,MATCH(--$C2,'Data 1'!A:A,0))

    and fill it down

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 17, 2019

KC_86 , it could be

=INDEX('Data 1'!B:B,MATCH(--$C2,'Data 1'!A:A,0))

and fill it down

Kim_Wennerberg's avatar
Kim_Wennerberg
Copper Contributor
May 17, 2019
Sergei,
Can you explain the double-dash used in the MATCH function? (--$C2)
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 17, 2019

    Kim_Wennerberg 

     

    Kim, there are numbers as text in column C, and in lookup column we have numbers. Double dash converts texts to numbers. Same as VALUE() here.

    • Kim_Wennerberg's avatar
      Kim_Wennerberg
      Copper Contributor
      May 17, 2019

      I am not finding it necessary to use double dash to convert "FALSE" to 0 in Excel Office 365.  I can just act as if FALSE is a 0 or TRUE is a 1 and use it in other formulas. 

      Cell A2 resolves to FALSE

      CELL A3 contains =A2*3 and displays 0

      Same behavior with TRUE.  Double dash seems no longer necessary.

      SergeiBaklan 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 17, 2019

        Kim_Wennerberg , that's not about TRUE and FALSE, that's about MATCH

        =MATCH("2",{1,2,3},0) returns #N/A
=MATCH(--"2",{1,2,3},0) returns 2

         

Resources