Forum Discussion

Mahmoudali's avatar
Mahmoudali
Copper Contributor
Nov 17, 2021
Solved

Help in lookup functions

I have simple table here which will be large one later with huge data. My question is i want to make function in the selected cell to search all table for specifc value and return corresponding data i...
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 18, 2021

    Mahmoudali 

    That could be

    =IF( MAX( ($C$2:$G$5=$H2)*( ROW($B$2:$B$5)-ROW($B$1) ) ) = 0,
     "no such",
     INDEX( $B$2:$B$5, MAX( ($C$2:$G$5=$H2)*(ROW($B$2:$B$5)-ROW($B$1) ) )  ) )
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 17, 2021

Mahmoudali 

Yes, that could be XLOOKUP like

=XLOOKUP(code, codes column, agents column)
  • Mahmoudali's avatar
    Mahmoudali
    Copper Contributor
    Nov 17, 2021
    When i select array coulmn c and d to look for number it returns error like #value but when i make it select on coulmn it returns correct value.
    But i want to select multiple columns to search in it because every agent will have multiple number
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 17, 2021

      Mahmoudali 

      Could you please illustrate with sample, file, not with screenshot?

      • Mahmoudali's avatar
        Mahmoudali
        Copper Contributor
        Nov 17, 2021
        Here is a sample file
        I want to design an formla in ??????? Cell
        That read the value in h2 and search for it in columns c,d,e,f,g and return the corresponding value from coulmn B. That is it i want to search the value in multiple coulmns and return one corresponding value
  • Mahmoudali's avatar
    Mahmoudali
    Copper Contributor
    Nov 17, 2021
    It does not work i tried it

Resources