LOOKUP CRITERIA

[ovadeso]

Hey guys, I have a question on the VLOOKUP and HLOOKUP functions. Basically i would like to know if you can combine both in the same formula. Lets say you have a table with the heading being the months from Jan. to Dec. and the 1st column being the years from 1996 to 2004, and you want to return a value from that table for a specific month and year based on a date that you entered in a separate sheet, how is that done? I know i can do a VLOOKUP and find the year and then specify a column # from which i want the value returned. But what if i don't want to specify a column # and want excel to look for the value under the month shown in the date? Is this possible?
Thanks for your suggestions.

 

[just_jon]

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1996	123.0	456.0	789.0	1122.0	1455.0	1788.0	2121.0	2454.0	2787.0	3120.0	3453.0	3786.0
3	1997	135.3	501.6	867.9	1234.2	1600.5	1966.8	2333.1	2699.4	3065.7	3432.0	3798.3	4164.6
4	1998	148.8	551.8	954.7	1357.6	1760.6	2163.5	2566.4	2969.3	3372.3	3775.2	4178.1	4581.1
5	1999	163.7	606.9	1050.2	1493.4	1936.6	2379.8	2823.1	3266.3	3709.5	4152.7	4595.9	5039.2
6	2000	180.1	667.6	1155.2	1642.7	2130.3	2617.8	3105.4	3592.9	4080.4	4568.0	5055.5	5543.1
7	2001	198.1	734.4	1270.7	1807.0	2343.3	2879.6	3415.9	3952.2	4488.5	5024.8	5561.1	6097.4
8	2002	217.9	807.8	1397.8	1987.7	2577.6	3167.6	3757.5	4347.4	4937.3	5527.3	6117.2	6707.1
9	2003	239.7	888.6	1537.5	2186.5	2835.4	3484.3	4133.2	4782.2	5431.1	6080.0	6728.9	7377.8
10	2004	263.7	977.5	1691.3	2405.1	3118.9	3832.7	4546.6	5260.4	5974.2	6688.0	7401.8	8115.6
11
12	1999
13	Mar
14	1050.2	1050.2
Sheet6

The formulas are:

A14:=INDEX(A1:M10,MATCH(A12,A1:A10,0),MATCH(A13,A1:M1,0))

B14:=VLOOKUP(A12,A1:M10,MATCH(A13,A1:M1,0),0)

 

[ovadeso]

Thanks a million, I haven't tried it yet but you,ve duplicated my situation excatly, except for the date format. I take it that i'll need to have the month and year in separate cells. The format Dec 2000 or 12/2000 in one cell would not work. If thats the case i'll simply split the date up into different cells. Thanks again.

 

[just_jon]

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1996	123.0	456.0	789.0	1122.0	1455.0	1788.0	2121.0	2454.0	2787.0	3120.0	3453.0	3786.0
3	1997	135.3	501.6	867.9	1234.2	1600.5	1966.8	2333.1	2699.4	3065.7	3432.0	3798.3	4164.6
4	1998	148.8	551.8	954.7	1357.6	1760.6	2163.5	2566.4	2969.3	3372.3	3775.2	4178.1	4581.1
5	1999	163.7	606.9	1050.2	1493.4	1936.6	2379.8	2823.1	3266.3	3709.5	4152.7	4595.9	5039.2
6	2000	180.1	667.6	1155.2	1642.7	2130.3	2617.8	3105.4	3592.9	4080.4	4568.0	5055.5	5543.1
7	2001	198.1	734.4	1270.7	1807.0	2343.3	2879.6	3415.9	3952.2	4488.5	5024.8	5561.1	6097.4
8	2002	217.9	807.8	1397.8	1987.7	2577.6	3167.6	3757.5	4347.4	4937.3	5527.3	6117.2	6707.1
9	2003	239.7	888.6	1537.5	2186.5	2835.4	3484.3	4133.2	4782.2	5431.1	6080.0	6728.9	7377.8
10	2004	263.7	977.5	1691.3	2405.1	3118.9	3832.7	4546.6	5260.4	5974.2	6688.0	7401.8	8115.6
11
12	1999		3/1/1999
13	Mar
14	1050.2	1050.2	1050.2
Sheet6

Here you go --

=VLOOKUP(YEAR(C12),A1:M10,MATCH(TEXT(C12,"mmm"),A1:M1,0),0)

 

[ovadeso]

Works absolutely perfectly. Thanks again.