CODEWITHSUNDEEP
excelexcel-2007excel-formula
BrianLender
BrianLender

Reputation: 531

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?

Upvotes: 4

Views: 10934

Answers (1)

barry houdini
barry houdini

Reputation: 46341

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)

Upvotes: 11

Related Questions