Search code examples
excelexcel-2007excel-formula

Find the number of a row that contains two specific values?

I have a table of information with multiple columns. The first column is "Date" (range A:A) and the second column is "Name" (range B:B). When I enter a date into cell Q1 and a name into cell R1, I need a formula that will find which row contains both of those values and output the row number.

I feel like I need to use the match function but can't quite figure it out. Does anyone know how to do this?

Solution

  • You can use MATCH in an "array formula" like this

    =MATCH(1,(A:A=Q1)*(B:B=R1),0)

    which needs to be confirmed with CTRL+SHIFT+ENTER

    ....or add an INDEX function and the formula can be entered normally

    =MATCH(1,INDEX((A:A=Q1)*(B:B=R1),0),0)