double vlookup in same function

[Guest]

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

 

[Biff]

Hi!

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

Biff

 

[Domenic]

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!

 

[Aladin Akyurek]

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),"")