Forum Discussion

MarkK777's avatar
MarkK777
Copper Contributor
Apr 13, 2019
Solved

Formula HELP!!

 Hi Everybody. I’m struggling with formula and wonder if you can help. I have 2 spreadsheets. The first one represents a list of payments made on a particular day of the month. The columns are dat...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 14, 2019

    Mark, you takes the value from 1D array and shall position row in it using two criteria (date and room #), not row and column.

     

    It could be

    =IFERROR(  INDEX($U$3:$U$19,  MATCH(1,  INDEX( ($A3=$T$3:$T$18)*(B$1=$S$3:$S$18),0),0 )  ), 0)

    INDEX within MATCH generates array of TRUE and FALSE (or 1 and 0) depend on criteria is met or not. Multiplication means AND condition.

    Please check in attached.

     

    MarkK777 

MarkK777's avatar
MarkK777
Copper Contributor
Apr 13, 2019
HI Sergei. As you can see in the attached file, I can't get the right value into the corresponding cells based on the room number and the date. The room number in the match function returns the room number, but the date in the match, returns relative row number.
So I kind of stuck here.
Thanks again for a quick response
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 14, 2019

Hi Mark - unfortunately there was no file attached, could you please repeat it.

MarkK777 

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Apr 14, 2019

      MarkK777 

      I would suggest a pivot table.

      Date in the columns area, Room # in the rows area and Pay in the values area.

       

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 14, 2019

      Mark, you takes the value from 1D array and shall position row in it using two criteria (date and room #), not row and column.

       

      It could be

      =IFERROR(  INDEX($U$3:$U$19,  MATCH(1,  INDEX( ($A3=$T$3:$T$18)*(B$1=$S$3:$S$18),0),0 )  ), 0)

      INDEX within MATCH generates array of TRUE and FALSE (or 1 and 0) depend on criteria is met or not. Multiplication means AND condition.

      Please check in attached.

       

      MarkK777 

      • MarkK777's avatar
        MarkK777
        Copper Contributor
        Apr 15, 2019

        SergeiBaklan 

        Sergei,

         

        THanks a lot.  It really helped

Resources