Forum Discussion
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 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
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) ) ) ) )
10 Replies
You can see how to solve the problem at: https://tinhoc39.com/thu-thuat-excel/ham-hlookup-trong-excel.html
- 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 numberCould you please illustrate with sample, file, not with screenshot?
- It does not work i tried it
