Search code examples
excelfunctionreturncell

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

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);" ")
Solution

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

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

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

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