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
It does not work i tried it

Resources