Forum Discussion

John Breeden's avatar
John Breeden
Copper Contributor
May 21, 2017
Solved

Spreadsheet Help with Formulas Across Several Columns

In cell B5 it currently says 3 Foot.  In different scenarios it can also say 6 Foot or Even.  I need cell C7 to reference the appropriate column on tab 1C in regards to Even, 3 Foot, or 6 Foot, then ...
Formulas & Functions
Need Help
  • Detlef_Lewin's avatar
    Detlef_Lewin
    May 21, 2017

    One possible solution:

    =INDEX('1C'!A:D,LOOKUP(B7,'1C'!A2:A509,ROW('1C'!C2:C509)),MATCH(B5,'1C'!1:1,0))

     

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
May 21, 2017

One possible solution:

=INDEX('1C'!A:D,LOOKUP(B7,'1C'!A2:A509,ROW('1C'!C2:C509)),MATCH(B5,'1C'!1:1,0))

 

  • John Breeden's avatar
    John Breeden
    Copper Contributor
    May 21, 2017

    That worked PERFECT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

     

    I have one more problem to solve with the same notebook.

     

    On the main spreadsheet (Gauge), the Columns TEMP, API, and CORRECT FACTOR.  I need cell K7 to get it's information based off the reference points of cell I7 and J7 from tab API.

     

    On the API tab the Temp is the far left column and ranges from 30-119 degrees.

     

    The API column header starts at B1:U1 with the corresponding APIs B2:B91 through U2:U91.

     

    Simply put, I need the Correcton Factor to cross reference the temperature in relation to the API.  This should work similar to the previous problem you just solved for me.

     

    If you have any questions, PLEASE don't hesitate to ask!  YOU'RE A GREAT HELP!!!!!!!!!!!!!

     

     

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      May 21, 2017

      This time a slightly different solution.

      =INDEX(API!$A$1:$U$91,MATCH(I7,API!$A:$A,0),MATCH(J7,API!$1:$1,0))

       

      • John Breeden's avatar
        John Breeden
        Copper Contributor
        May 22, 2017

        PERFECT AGAIN!!!!

         

        Sorry for not replying sooner but I was out on a run at the end of the day.  I just checked a few minutes ago this morning and it's perfect!!!!

         

        I've been struggling with this for quite some time now but you cinched it up quickly.

         

        After looking at it I was wondering if there was a way to have the 3 Foot, 6 Foot, and Even in a dropdown box where you currently have to manually type it.  If you could help me with that one it would be a homerun!!!  If not then it's still a homerun because you just don't know how long I've been trying to make this work.

         

        Again, thank you very, very, very much!!!!

         

        P.S.  Whatever your employer is paying you isn't enough, if it were up to me I would easily double your pay!!!

Resources