Pavan Kumar Sistla
Member
Dear All,
I have a sample file which I am attaching herewith. My query is below.
There are 2 sheets named Matrix and Data in the file. 1st sheet named Matrix has Area, Building name along with range of unit sizes (Studio, 1 Bed, 2 Bed etc.). 2nd sheet named Data has the Area, Building name along with unit sizes of buildings. Accordingly, I need to retrieve the Bed levels for those based on the matrix. For eg: Area name is M & Building name is m and the unit size is 1600, I need to check in matrix sheet whether 1600 falls in which range whether it is 2 bed or 3 bed and I need to populate the bed number in the column named "Beds" of Data sheet.
I was trying to use Index & Match. However, the column section of the formula has to be dynamic is what I feel. The row no. retrieved has to be used as the array for match formula in column section. Tried to use concatenate, but not working.
Could anyone please help quickly. Its on urgent basis.
Thank you very much
I have a sample file which I am attaching herewith. My query is below.
There are 2 sheets named Matrix and Data in the file. 1st sheet named Matrix has Area, Building name along with range of unit sizes (Studio, 1 Bed, 2 Bed etc.). 2nd sheet named Data has the Area, Building name along with unit sizes of buildings. Accordingly, I need to retrieve the Bed levels for those based on the matrix. For eg: Area name is M & Building name is m and the unit size is 1600, I need to check in matrix sheet whether 1600 falls in which range whether it is 2 bed or 3 bed and I need to populate the bed number in the column named "Beds" of Data sheet.
I was trying to use Index & Match. However, the column section of the formula has to be dynamic is what I feel. The row no. retrieved has to be used as the array for match formula in column section. Tried to use concatenate, but not working.
Could anyone please help quickly. Its on urgent basis.
Thank you very much