-2

I have two columns

Column A   Column B 
Apple       A
banana      B
Grape       C
Apple       D
Banana      F

Now I want to find the row number for row which has data Apple & D.

Is their a way to use Match function to get the row number?

Ram
  • 3,092
  • 10
  • 40
  • 56
SAM244776
  • 1,375
  • 6
  • 18
  • 26
  • This is a duplicate of both [This](http://stackoverflow.com/questions/19307976/find-the-number-of-a-row-that-contains-two-specific-values) AND [This](http://stackoverflow.com/questions/9110429/selecting-a-row-in-excel-based-on-specific-values-in-2-columns) – user2140261 Apr 29 '14 at 19:57

3 Answers3

1

You can use this one:

=LOOKUP(2,1/(A1:A5="Apple")/(B1:B5="D"),ROW(A1:A5))

or non-volatile version (if your ranges starts from first row):

=MATCH(2,1/(A1:A5="Apple")/(B1:B5="D"))

with array entry (CTRL+SHIFT+ENTER).

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
1

Alternate, does not require array entry:

=MATCH(TRUE,INDEX(A1:A5&B1:B5="Apple"&"D",),0)
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
0

Try this !!

=SUMPRODUCT((MATCH(1,(A1:A5="Apple")*(B1:B5="D"),0)))

Array formula CTRL+SHIFT+ENTER

Prabhat Srivastava
  • 197
  • 1
  • 7