double vlookup in same function

S

Streep

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
 
D

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!
 
A

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top