Lookup multiple criteria in table and give row + culmn as result

Try this...

Data Range

	A	B	C	D	E	F	G
1		1	2	3	4	5	6
2	a	Available
3	b		Available	Available
4	c				Available
5	d				Available
6	e					Available
7	f						Available
8
9
10	Available (return values)
11	a	1
12	b	2
13	b	3
14	c	4
15	d	4
16	e	5
17	f	6
18

This array formula** entered in A11:

=IFERROR(INDEX(A$2:A$7,SMALL(IF(B$2:G$7="Available",ROW(B$2:G$7)),ROWS(A$11:A11))-ROW(B$2)+1),"")

This array formula** entered in B11:

=IFERROR(INDEX(B$1:G$1,SMALL(IF(INDEX(B$2:G$7,MATCH(A11,A$2:A$7,0),0)="Available",COLUMN(B2:G7)),COUNTIF(A$11:A11,A11))-COLUMN(B2)+1),"")

** 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.

Select A11:B11 and copy down until you get blanks.

Originally Posted by bidsinga

isn't it neccessarry to add dollar signs in COLUMN(B2:G7)?

No.

You're copying the formula downwards so the column references will not change. Also, the row numbers are irrelevant so if they change it doesn't make any difference. I guess we could add dollar signs to the rows just to make the formula look more intuitive:

COLUMN(B$2:G$7)