Fix the index match code

%3CLINGO-SUB%20id%3D%22lingo-sub-2085375%22%20slang%3D%22en-US%22%3EFix%20the%20index%20match%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2085375%22%20slang%3D%22en-US%22%3E%3DF9*%40INDIRECT(ADDRESS(MATCH(D9%2CSheet1!%24B%3A%24B%2C0)%2CMATCH(C9%2CSheet1!%242%3A%242%2C0))%2CTRUE)%3CBR%20%2F%3E%3CBR%20%2F%3EData%20is%20matching%202%20conditions.%20Can%20some%20one%20fix%20it.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2085375%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2086074%22%20slang%3D%22en-US%22%3ERe%3A%20Fix%20the%20index%20match%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2086074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F938439%22%20target%3D%22_blank%22%3E%40NadeemAltaf%3C%2FA%3E%26nbsp%3BWould%20be%20extremely%20helpful%20if%20you%20could%20share%20a%20workbook%20with%20some%20(non-confidential)%20data%20in%20it%20so%20that%20we%20can%20see%20the%20formula%20at%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
=F9*@INDIRECT(ADDRESS(MATCH(D9,Sheet1!$B:$B,0),MATCH(C9,Sheet1!$2:$2,0)),TRUE)

Data is matching 2 conditions. Can some one fix it.
2 Replies

@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.

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.