Multiple IF and VLOOKUP

[Anto111]

Hi guys,

I have a large workbook organising heart rate data for a sports team.

I am creating a seperate smaller spreadsheet as a one page summary.

On the summary sheet, what I am doing is setting up a facility whereby
entering a name in F2 will lookup that name on the larger workbook and return
the appropriate heart rate values in the larger sheet. I have managed this
but would now like to take it a step further whereby I can also enter a
weekday in G2 on the summary and it will look up the relevant player name as
per F2 along with their data for the particular day as designated in G2.

At the moment I can only get data for Monday using the formula:

=IF($G$2="Monday",VLOOKUP($F$2,'[Heart Rate.xlsx]Week 1'!$C$7:$NF$36,331,0))

What I have tried entering is:

=IF($G$2="Monday",VLOOKUP($F$2,'[Heart Rate.xlsx]Week
1'!$C$7:$NF$36,100,IF($G$2="Tuesday",VLOOKUP($F$2,'[Heart Rate.xlsx]Week
1'!$C$7:$NF$36,200))))

This however is only returning values for Monday and is returning FALSE when
I enter Tuesday in G2.

Thanks in advance for your help,

cheers guys,

Ant

 

[Bob Phillips]

=INDEX($C$7:$NF$36,MATCH(1,($F$2,'[Heart Rate.xlsx]Week
1'!$C$7:$NF$331,0),330+MATCH($G$2,{"Monday","Tuesday","Wednesday","Thursday","Friday"},0)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)