Excel: how do i lookup a cell number by containing text and return it-CodePudding

my problem is the following: I need a formula that takes a text value from column A and checks if this value is in column Q. If yes it should return the value of the associated cell in column P.

But i already fail to return the cell address of column Q if it matches.

This is my formula:

=IF(COUNTIF($Q$2:$Q$577;A222);MATCH(A222;$Q$2:$Q$577M;0);" ")

CodePudding user response：

You can use INDEX and MATCH to get this output like Rory suggested

=IFERROR(INDEX($P$2:$P$577;MATCH(A222;$Q$2:$Q$577M;0));"")

If you have O365, you can also use Xlookup like this:

=XLOOKUP(A222;$Q$2:$Q$577;$P$2:$P$577)