Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
981 views
in Technique[技术] by (71.8m points)

excel - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...