Lookup / Match ?

[Mike B]

Hi,
I would be gratefull for any help with the following

Mon Mon Tues Tues Wd
1 A B A B A
2 dave Harry Nick Ed Jack
3 Mark Ian Mike Pete Tom
4 Ian Dave Marc Harry Edd

How would I lookup /Match the top two rows and 1st column to return a result
from the names. ie Mon, B,3, Would give me the name Ian.


Thanks
Mike B

 

[Domenic]

Assuming that A1:F5 contains the table, H2 contains Mon, I2 contains B,
and J2 contains 3, try the following formula that needs to be confirmed
with CONTROL+SHIFT+ENTER...

=INDEX($B$2:$F$5,MATCH(J2,$A$2:$A$5,0),MATCH(1,IF($B$1:$F$1=H2,IF($B$2:$F
$2=I2,1)),0))

Hope this helps!

 

[Stefi]

=INDEX(A1:F5,MATCH(G3,A1:A5,0),SUMPRODUCT(--(B1:F1=G1),--(B2:F2=G2),COLUMN(B1:F1)))

Where
G1 - Mon, ...
G2 - A,B
G3 - 1,2,3 ...

Regards,
Stefi

„Mike B†ezt írta:

 

[Mike B]

?Appologies for the dely in replying, this was a great helpthank you. May I
ask a further question, what would I add to this function to get the
information from another worksheet in my workbook ?

 

[Mike B]

Appologies for the delay in replying , it's a great help thanks

Mike B

 

[Domenic]

Assuming that Sheet2 contains the source data/table, try...

=INDEX('Sheet2'!$B$2:$F$5,MATCH(J2,'Sheet2'!$A$2:$A$5,0),MATCH(1,IF('Shee
t2'!$B$1:$F$1=H2,IF('Sheet2'!$B$2:$F$2=I2,1)),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Mike B†ezt írta:

 

[Mike B]

Hi,
This works great when I enter the sorce worksheet directly into the
function. Ie 'Sheet2', however If I enter'Week2' into a cell and enter that
cell in place of 'Week2' into the fuction ,no matter how I try it will not
work, any suggestions please

Thanks

Mike B

 

[David Biddulph]

Use the INDIRECT function.
--
David Biddulph

Hi,
This works great when I enter the sorce worksheet directly into the
function. Ie 'Sheet2', however If I enter'Week2' into a cell and enter
that
cell in place of 'Week2' into the fuction ,no matter how I try it will not
work, any suggestions please

Thanks

Mike B