Vlookup with variable table array?

[Lifesaver]

I am attempting to create a lookup function that searches for a value in a table array that varies according to what is found in another cell.

See in the simplified example below, lookup_value is C17 and, depending of the code typed at C18, the table array would start from the cell that contains this value (F3:H15 in this example) and it would return the equivalent amount of hours in the last column (1185.71) as the result. Obviously, my list would remain sorted to allow the 4th argument (range_lookup) to be "True".

Hourly Employees Salary Rates Chart (for MBONDU).xls
	A	B	C	D	E	F	G	H
1
2		FTAB	FTBB	FTCB	PTAB	PTBB	PTCB	Hours
3		10.50	9.50	11.50	10.50	9.50	11.50	0
4		10.50	9.50	11.50	11.55	10.65	12.90	385.71
5		11.55	10.65	12.90	11.55	10.65	12.90	514.29
6		11.55	10.65	12.90	12.47	11.17	13.70	1185.71
7		12.47	11.17	13.70	12.47	11.17	13.70	1314.29
8		12.47	11.17	13.70	13.31	11.91	14.60	1985.71
9		13.31	11.91	14.60	13.31	11.91	14.60	2114.29
10		13.31	11.91	14.60	14.11	12.31	15.50	2785.71
11		14.11	12.31	15.50	14.11	12.31	15.50	2914.29
12		14.11	12.31	15.50	15.00	13.38	16.40	3585.71
13		15.00	13.38	16.40	15.00	13.38	16.40	3714.29
14		15.00	13.38	16.40	16.00	14.25	17.40	4385.71
15		16.00	14.25	17.40	16.00	14.25	17.40	4514.29
16
17		Rate:	11.17
18		Code:	PTBB
19		Hours:	?
Sheet2

Any help that you could provide would be greatly appreciated. Thank you!

 

[Peter_SSs]

Lifesaver

Like this?

D18: =MATCH(C18,B2:G2,0)
Tells us what column in the table the C18 value is.
C19: =VLOOKUP(C17,OFFSET(B3:B15,,D18-1,,8-D18),8-D18,0)

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1
2		FTAB	FTBB	FTCB	PTAB	PTBB	PTCB	Hours
3		10.5	9.5	11.5	10.5	9.5	11.5	0
4		10.5	9.5	11.5	11.55	10.65	12.9	385.71
5		11.55	10.65	12.9	11.55	10.65	12.9	514.29
6		11.55	10.65	12.9	12.47	11.17	13.7	1185.71
7		12.47	11.17	13.7	12.47	11.17	13.7	1314.29
8		12.47	11.17	13.7	13.31	11.91	14.6	1985.71
9		13.31	11.91	14.6	13.31	11.91	14.6	2114.29
10		13.31	11.91	14.6	14.11	12.31	15.5	2785.71
11		14.11	12.31	15.5	14.11	12.31	15.5	2914.29
12		14.11	12.31	15.5	15	13.38	16.4	3585.71
13		15	13.38	16.4	15	13.38	16.4	3714.29
14		15	13.38	16.4	16	14.25	17.4	4385.71
15		16	14.25	17.4	16	14.25	17.4	4514.29
16
17		Rate:	11.17
18		Code:	PTBB	5
19		Hours:	1185.71
20
VLOOKUP

 

[just_jon]

I think you're looking for

=INDEX(H3:H15,MATCH(C17,INDEX(B3:G15,0,MATCH(C18,B2:G2,0))))

Anchor refs as needed.

 

[Lifesaver]

Thank you guys!

To just_jon:

It work only if I specify H3:H15 instead of H2:H15 in the first argument of the INDEX function. Otherwise, it does not return the correct amount of hours. Do you think this is a correct fix?

=INDEX(H2:H15,MATCH(C17,INDEX(B3:G15,0,MATCH(C18,B2:G2,0))))


To Peter_SSs:

Your solution is great with the only exception that I want to avoid refering to another cell (D18) in the function. If I include it in my function, it becomes rather complex:

=VLOOKUP(C17,OFFSET(B3:B15,,MATCH(C18,B2:G2,0)-1,,8-MATCH(C18,B2:G2,0)),8-MATCH(C18,B2:G2,0),0)

My example was simple, but the reality is that my database contains about 30,000 records by about 25 columns. If there is a way to simplfy it, feel free to let me know.

Thanks anyway to both of you, you're saving me a lot of work by helping me out! Have a great day!

 

[NBVC]

If your looking for the 1st Match only, then add a 0 for the match type instead of 1 (or omitted).

=INDEX($H$3:$H$15,MATCH($C$19,INDEX($B$3:$G$15,0,MATCH($C$20,$B$2:$G$2,0)),0))

 

[Lifesaver]

Thank you very much NBVC! It works nicely!

Many thanks to just_jon and Peter_SSs as well for bringing me to the correct path!

LifeSaver ...saved!