double lookup

[cucaracha]

Dear all,

I am trying to do a double lookup on the following table:
The range of the table is B2:I50

HORA DOM SEG TER QUA QUI SEX SAB
0:00 V V FV FV FV FV FV
0:30 V V V V V V V
1:00 V V V V V V V
1:30 V V V V V V V
2:00 V V V V V V V
2:30 V V V V V V V
3:00 V V V V V V V
3:30 V V V V V V V
4:00 V V V V V V V
4:30 V V V V V V V
5:00 V V V V V V V
5:30 V V V V V V V
6:00 V V V V V V V
6:30 V V V V V V V
7:00 V V V V V V V
7:30 V FV FV FV FV FV V
8:00 V FV FV FV FV FV V
8:30 V FV FV FV FV FV V
9:00 V FV FV FV FV FV V
9:30 V FV FV FV FV FV V
10:00 V FV FV FV FV FV FV
10:30 V FV FV FV FV FV FV
11:00 V FV FV FV FV FV FV
11:30 V FV FV FV FV FV FV
12:00 V FV FV FV FV FV FV
12:30 V FV FV FV FV FV FV
13:00 V FV FV FV FV FV FV
13:30 V FV FV FV FV FV V
14:00 V FV FV FV FV FV V
14:30 V FV FV FV FV FV V
15:00 V FV FV FV FV FV V
15:30 V FV FV FV FV FV V
16:00 V FV FV FV FV FV V
16:30 V FV FV FV FV FV V
17:00 V FV FV FV FV FV V
17:30 V FV FV FV FV FV V
18:00 V FV FV FV FV FV V
18:30 V FV FV FV FV FV V
19:00 V FV FV FV FV FV FV
19:30 V FV FV FV FV FV FV
20:00 V FV FV FV FV FV FV
20:30 V FV FV FV FV FV FV
21:00 V FV FV FV FV FV FV
21:30 V FV FV FV FV FV FV
22:00 V FV FV FV FV FV FV
22:30 V FV FV FV FV FV V
23:00 V FV FV FV FV FV V
23:30 V FV FV FV FV FV V

and on cell L2 I put the hour and on L3 I put the day. On L4 should give the result. for instance on 04:30 of SAB the result should be V.
I am writing the following array formula but is giving me an error:
=OFFSET(B2:I50;MATCH(L2;OFFSET(B2:I50;0;0;ROWS(B2:I50);1);0)-1;MATCH(L3;OFFSET(B2:I50;0;0;1;COLUMNS(B2:I50));0)-1)

Can someone give me some help?
I am using EXCEL 2003.
Best regards,
Cucaracha

 

[VoG]

Try

=INDEX(B2:I50,MATCH(L2,B2:B50,0),MATCH(L3,B2:I2,0))

 

[Aladin Akyurek]

Assuming that B1:I1 houses the headers (i.e., HORA, DOM, etc.), try...

=INDEX($C$2:$I$50,MATCH(L2,$B$2:$B$50,0),MATCH(L3,$C$1:$I$1,0))

where L2 houses a time value like: 04:30 and L3 a day like SAB.

 

[cucaracha]

Hi VoG,

Thank you for the answer but is giving error #N/A.

Regards,
Cucaracha

 

[VoG]

It worked for me

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
2	HORA	DOM	SEG	TER	QUA	QUI	SEX	SAB			07:30
3	00:00	V	V	FV	FV	FV	FV	FV			TER
4	00:30	V	V	V	V	V	V	V			FV
5	01:00	V	V	V	V	V	V	V
6	01:30	V	V	V	V	V	V	V
7	02:00	V	V	V	V	V	V	V
8	02:30	V	V	V	V	V	V	V
9	03:00	V	V	V	V	V	V	V
10	03:30	V	V	V	V	V	V	V
11	04:00	V	V	V	V	V	V	V
12	04:30	V	V	V	V	V	V	V
13	05:00	V	V	V	V	V	V	V
14	05:30	V	V	V	V	V	V	V
15	06:00	V	V	V	V	V	V	V
16	06:30	V	V	V	V	V	V	V
17	07:00	V	V	V	V	V	V	V
18	07:30	V	FV	FV	FV	FV	FV	V
19	08:00	V	FV	FV	FV	FV	FV	V
20	08:30	V	FV	FV	FV	FV	FV	V
21	09:00	V	FV	FV	FV	FV	FV	V
Sheet2

 

[cucaracha]

Thank you both,

But is still giving the same error.
I don't know if it related with the fact that the values in HORA are time values?

Regards,
Cucaracha

 

[cucaracha]

Dear both,

Now works.
I replaced the match type from 0 to 1.

1000 thank you

Regards,
Cucaracha

 

[cucaracha]

I am back again.

The formula works but when I need to find a value related with time 0:00 it gives me the error again.

Any idea?

Best regards,
Cucaracha

 

[VoG]

It works fine for me with a time of 00:00

 

[cucaracha]

Dear VoG,

I don't know why but for me doesn't work.
The only way is changing the match type from 0 to 1 and with this the 0:00 gives error.

Regards,
cucaracha