Dashboard Sheet using Index,Match,Offset function to retrieve Data from another sheet

see the attached file

Hi, refer the attachment, array formulas were used (to be entered with ctrl+shift+enter) not just enter

@nflsales

Very close to what i seek, but few observation/correction required, Brother.

first, what if the data rows (currently upto 408 rows) changes to only 300 rows, or 500 rows or someday, upto 1000 or so. Flexibility is required, please.

Second, my mistake, through oversight, i took wrong range, in Cell F21, and G21, which should have read data from =Input!K13 (wrongly taken as =Input!K14), and =Input!M13 (wrongly taken as =Input!M14).


Can you please modify the formula, Thanks. Later, i would like to convert this into macro, and once, correctly retrieved, would love to have only values, instead of formula.

Thanks


@boopathiraj, thanks for spotting my logical error.

SEE THE ATTACHED FILE
One row was Inserted in Input sheet for headings

Hi Analystbank

Now try this attached i have updated function , now there is no matter how many rows will come everyday just drag the function u will get output

Once done with drag later u can paste values.

@nflsales, Your solution gives the desired output, but see,

1)data in Input sheet is fabricated, and manipulated through macros from several sheets, and i cant't insert row at begining as header, as even in those input sheet, i perform many other tests, so, no header, i can insert or change the RANGe

Only flexibility that i need is that, Data Row range could vary every day so, how to remove that restriction, as in your revised solution, range from INPUT sheet, is still confined to read only upto row 409 in SMALL function. !!!

Thanks a lot, perfectly alright, Brother


@Naveed, thanks for your suggestion, but i am not able to open the excel itself, as i use Version 2003, and have no compaitible / converter to open higher version.

see the attached file

Thanks every1.

@nfsales thanks for your suggested solution in post #8 above. I will modify in my main workbook, and would try to put it through macro or so. Thanks

Just additional thought.

Now, in Sheet - Output, if i make similar matrix as in range A5:G10, (solution already given), in RANGE K5:Q10, but want to pull data in sameway from from sheet named INPUT1, how to modify the existing formula?

I just copy pasted formula, and changed the sheet name, but it does not give me the output, so i guess, index, small function also need some modifications

Sample file attached

thanks for your feedback