Double Match

[John]

A A B B B B B C C C
M S M N O R S M P S
1 1 4 5 6 7 10 11 12 13 14
2 15 18 19 20 21 24 25 26 27 28
3 29 32 33 34 35 38 39 40 41 42
4 43 46 47 48 49 52 53 54 55 56
5 57 60 61 62 63 66 67 68 69 70


Lets say i have data like above (where there is a main header -A,B, or
C) and then a sub header, (M/S/N/O/R/P). Then there is a date in the
first column (indicated here by the numbers 1-5 down the left side).

A B C
4
5
6


I then have a table like the one above. What I'm trying to do is get
the "S" value returned for each of the A, B, C main headers on the
given dates (4,5 and 6 in this example). What i've tried to do is
write a nested matching/vlookup function, but I have failed thus far.
IE, write a vlookup function that looks up the date. Have the column
it uses be a match looking for S in the sub header row where the
lookup array is returned using a match of the main header row and then
adding a few columns. Any ideas on how I can accomplish this?

And I hope I explained everything well enough for everyone to
understand =)

 

[Peo Sjoblom]

http://www.contextures.com/xlFunctions03.html#IndexMatch2



--


Regards,


Peo Sjoblom

 

[ryguy7272]

Just taking a stab at it:
=INDEX(B2:J7,MATCH(B10,INDEX(A2:A7,,1),0),MATCH(A10,INDEX(B1:J1,1,),0))

Look here for a full explanation of the Index/Match function:
http://www.contextures.com/xlFunctions03.html#IndexMatch


Regards,
Ryan---

 

[Max]

Another play to tinker with ..
illustrated in this sample:
http://www.freefilehosting.net/download/3i336
Double Match.xls

Source data as posted assumed within A1:K7

In M2: S
In N2 across: A, B, C ...
In M4 down: 4, 5, 3 ...

Array-enter in N3, copy across/fill down to P3
=INDEX(OFFSET($A$3:$A$7,,MATCH(1,($B$1:$K$1=N$2)*($B$2:$K$2=$M$2),0)),MATCH($M3,$A$3:$A$7,0))

 

[Max]

Clarification:

Array-enter in N3, copy across/fill down to P3:
=INDEX(OFFSET($A$3:$A$7,,MATCH(1,($B$1:$K$1=N$2)*($B$2:$K$2=$M$2),0)),MATCH($M3,$A$3:$A$7,0))

"Array-enter" basically means
to press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER

---

 

[John]

Clarification:


"Array-enter" basically means
to press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---  

Worked like a charm. Thanks for the help! Now to understand exactly
how you did it =)

 

[Max]

Welcome, John