I want to perform a vlookup using three criteria to return data from another worksheet (within the same workbook) and I'm trying to follow the advice to use index/match to do so.
I want to retrieve the answer to specific questions in Output sheet based on three criteria in Level 4 sheet as follows:
Output Level 4
criteria 1 (text) is in the range A2:A184 A14
criteria 2 (text) is in the range C2:C184 C14
criteria 3 (question no e.g 4.01.01) K2:K184 K14
and the answers which I want are contained within range N2:N184 in Output sheet.
The formula which I'm using is:
<tbody>
</tbody>
which returns #N/A even though the data definitely exists in the Output sheet.
Can somebody please tell me where I'm going wrong or is there some limitation in retrieving data from one worksheet based on criteria in another?
Any help greatly appreciated.
I want to retrieve the answer to specific questions in Output sheet based on three criteria in Level 4 sheet as follows:
Output Level 4
criteria 1 (text) is in the range A2:A184 A14
criteria 2 (text) is in the range C2:C184 C14
criteria 3 (question no e.g 4.01.01) K2:K184 K14
and the answers which I want are contained within range N2:N184 in Output sheet.
The formula which I'm using is:
{=INDEX(Output!$N$2:$N$184,MATCH(1,(A14=Output!$A$2:$A$184)*(C14=Output!$C$2:$C$184)*(K14=Output!$K$2:$K$184),0))} |
<tbody>
</tbody>
which returns #N/A even though the data definitely exists in the Output sheet.
Can somebody please tell me where I'm going wrong or is there some limitation in retrieving data from one worksheet based on criteria in another?
Any help greatly appreciated.