Using the MATCH function to look for multiple conditions

[bananajelly]

This is my data:

R1 A x
R2 A y
R3 A z
R4 B x
R5 B y
R6 B z


The letters A and B are in Column G, and the x y z are in Column H. I want to figure out the row that contains both the letters B and y. Not just B or y, but B AND y.

Assume that "B" is in Cell A1 and "y" is in Cell A2.

I tried this:

=MATCH(1,(A1=G1:G6)*(A2=H1:H6),0)

I should get "5" as an output, but I just get "#VALUE!"

I also tried this:

=MATCH(A1&A2,G1:G6&H1:H6,0)

Still just get "#VALUE!"

I also tried this:

=MATCH(A1,(If(H1:H6, G1:G6),0)

Still fail.

Lastly, how do you post these excel questions on here without the html code. I can't create the table with the html code.

[ConneXionLost]

Confirm your second formula as an array formula with Ctrl-Shift-Enter instead of just enter.

[bananajelly]

Thanks. That worked!

[Tony Valko]

Do you want the absolute row number or the relative row number? Relative being the row number relative to the location of the lookup values within the referenced range. If your data was in the range G10:H15 the absolute row number would be 14 (the lookup values are physically located on row 14) and the relative row number would be 5 (the lookup values are located on row 5 of the referenced range).

Here's another one that returns the relative row number...

Data Range

	A	B	C	D	E	F	G	H
1	B						A	x
2	Y						A	y
3							A	z
4	5						B	x
5							B	y
6							B	z
7	-----	-----	-----	-----	-----	-----	-----	-----

This array formula** entered in A4:

=MATCH(A2,IF(G1:G6=A1,H1:H6),0)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.