Jan 21 2021 06:43 PM
Jan 22 2021 12:58 AM
@NadeemAltaf Would be extremely helpful if you could share a workbook with some (non-confidential) data in it so that we can see the formula at work.
Jan 22 2021 05:13 AM
At face value, you just need to use an INDEX instead of an INDIRECT.
It looks like you are matching D9 in a whole column (B) and matching C9 in a whole row (row 2) to find a value from a reference table at a given x,y position
So if D9 matches the 5th row of column B, and C9 matches the 6th column of row 2, are you trying to return the value from R5C6 = E6?
In general, you don't want to use match on whole columns and rows. To make life easier I would put the reference data in an Excel table, but for the sake of argument, let's say the data is in Sheet1!B2:Z999
Then you would want to use something like:
=index(Sheet1!$B$2:$Z$999, match(D9,$B$2:$B$999,0), match(C9,Sheet1!$B$2:$Z$2) )
which roughly translates as:
in <this area of data> fetch the item from <row which matches the row label> and <column which matches the column label).
Hope this helps.