Help in lookup functions

[Mahmoudali]

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 in column. For example if entered 555555 it return agent1 in the next cell. . Note i dont want to put all data in first column as it would be dificult to write thousnds of data there. I want excel search for value in all table and return corresponding name.my numbers will be different no similar numbers this just an example. I think the function could be xlookup or mixing betweenvlook,hlook,index,match or something ealse i cant do it.how could this be done

[#2]

@Mahmoudali 

Yes, that could be XLOOKUP like

=XLOOKUP(code, codes column, agents column)

[#3]

It does not work i tried it

[#4]

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

[#5]

@Mahmoudali 

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

[#6]

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

[#7 · Accepted solution]

@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) ) )  ) )

[#8]

I worked for me thank u very much. I used nested xlooup function too. They now both work thanks for the idea.

[#9]

@Mahmoudali , you are welcome

[#10]

Please can we do this formula on coulmns not just cells

[#11]

You can see how to solve the problem at: hlookup trong excel