I am trying to create a quick page to view a schedule from a list of teams and dates and locations.

The format of the dates and locations do not change, however the teams (home and away) do.

So basically I want to search for a team in a row, (the team name is selectable via data validation dropdown and could be in either Column E or F, of Row 4, (row 4 is the date the team is playing), (row 3 is the location and time, See picture). and I want to return the location (Row 3 is a merged cell with column E & f of Row 3).

I want to do that separately for each row, as each row is a separate game date.

 D        E          F               G                    H
2)         Harry Jerome         
3)  Year       HAR - 6:45am                    HAR - 7:45am 
4)  2016    VAN 12U-2   NVA 12U-2         COQ 12U-2        NVA 12U-2
5)  2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.
6)  2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.
7)  2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.
8)  2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.
9)  2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.
10) 2016    NVA 14U-2   SUR 14U-2       SUR 12U-2          VAN 12U-2
11) 2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.
12) 2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.
13) 2016    Not. Ava.   Not. Ava.       Not. Ava.          Not. Ava.

Eg, I want to search for a value in my dropdown "Van 12U-2" in row 4, and return "Har - 6:45am" in row 3. That will bring my time for that rows date. Row 10 is a different date, I want to do the same formula but now "Van 12U-2" is now in for 10 Column H, and I still want it to return the value in Row 4 column H of "HAR - 7:45am".

  • your question isn't really clear now. Where do you want to place the formula? what do you mean by "want to search for a value in Row 4" – Máté Juhász Jan 20 '16 at 21:13
  • Your sample data makes no sense. What is the relationship between your input and output? Also, please update your question to include what you have already tried. This isn't a code writing service and we expect askers to demonstrate research effort. – Kyle Jan 20 '16 at 21:28
  • Sorry, Lets try again. – dennis Jan 21 '16 at 3:02
  • Sorry, Lets try again. My apreadsheet goes till column AD. Column E & F are teams, E - Host Team, F - Guest team. Row 3 is the Location and time of the Game. I want to be able to search each row individually, Lets say row 10 only , I want to search for Team "NVA 14U-2". I want the value returned from that search to come from Row 3, (HAR - 6:45AM is the value I want returned). because each Row is a different date of a game. My problem Is I cannot seem to get the value of a cell that is above my search. – dennis Jan 21 '16 at 3:12
  • (=HLOOKUP(D2,Schedule!E4:J14,11,FALSE)). That is the formula I have tried. and it only works if I copy my row 3 to the bottom of my page. but then the search also searches the whole table, but I only want it to search the Row!. My second problem is, if the team name I want to search for is in column F. I cannot get the value that I want in column E because E & F are merged. It returns a 0. – dennis Jan 21 '16 at 3:13

If you insert a column after column F and add the formula on each row < =F3 > or whichever cell holds the venue and time data, then do the same for the other columns of data ( after column H < =H3 > or whichever cell holds the venue and data for the right hand side of the page ), these added columns can then be hidden. You can then search by using the Vlookup function in the selected range and tell it which column you want the data retrieved from eg =Vlookup("VAN 12-U2",E4:G13,3,FALSE) This will then return the venue and time data

  • Thanks Gary, that would possibly fix the merging solution. however. On 1 row of my chart there are 13 different games in 13 different locations. Eg Columns E & F (host team E and away team F) are one game in one location..... G & H are is another game in another location..... I & J are in a different location. On any given Row, my team name could be under columns E&F on row 2 for one day (lets say column E), but it could be under columns I & J (lets say J) for row 3.. So for your formula above, I would need it to go from E4: to AD4 cause I only want to search one row at a time. – dennis Jan 21 '16 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.