double vlookup in same function
 

I need to look up values in a tabel that has subtables.
Example:
35376 WEEK 22
MONTH 33
MIN 11
TTIN 44
35377 WEEK 55
MONTH 88
MIN 77
TTIN 99
I enter 35377, MIN and the function should then return 77
I cannot use the system where the WEEK, MONTH etc are in separate columns
such as
WEEK MONTH
35376 22 33
35377 55 88

Hi!

How is this table layed out? Can't tell for sure from the post.

Biff

"Streep" wrote in message
...
I need to look up values in a tabel that has subtables.
Example:
35376 WEEK 22
MONTH 33
MIN 11
TTIN 44
35377 WEEK 55
MONTH 88
MIN 77
TTIN 99
I enter 35377, MIN and the function should then return 77
I cannot use the system where the WEEK, MONTH etc are in separate columns
such as
WEEK MONTH
35376 22 33
35377 55 88

Assumptions:

A1:C8 contains your table

Each record is contained in 4 rows


Formula:

=VLOOKUP(F1,OFFSET(A1,MATCH(E1,A1:A8,0)-1,1,4,2),2,0)

....where F1 contains MIN, and E1 contains 35377. Adjust the range
accordingly.

Hope this helps!

In article ,
Streep wrote:

I need to look up values in a tabel that has subtables.
Example:
35376 WEEK 22
MONTH 33
MIN 11
TTIN 44
35377 WEEK 55
MONTH 88
MIN 77
TTIN 99
I enter 35377, MIN and the function should then return 77
I cannot use the system where the WEEK, MONTH etc are in separate columns
such as
WEEK MONTH
35376 22 33
35377 55 88

Let A2:C21 be the data area.
Assuming that each subtable has the entries WEEK, MONTH, MIN, and TTIN
and F2 houses a lookup value like 35377 and G2 a lookup value like MIN...

In H2 enter:

=MATCH(F2,$A$2:$A$21,0)

In E2 enter:

=IF(ISNUMBER(H2),VLOOKUP(G2,INDEX($B$2:$B$21,H2):$ C$21,2,0),"")

Streep wrote:
I need to look up values in a tabel that has subtables.
Example:
35376 WEEK 22
MONTH 33
MIN 11
TTIN 44
35377 WEEK 55
MONTH 88
MIN 77
TTIN 99
I enter 35377, MIN and the function should then return 77
I cannot use the system where the WEEK, MONTH etc are in separate columns
such as
WEEK MONTH
35376 22 33
35377 55 88