Can't get index/match to work

AndyB63

New Member
Joined
Feb 27, 2015
Messages
23
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:

{=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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Control+shift+enter, not just enter:

=INDEX(Output!$N$2:$N$184,MATCH(1,IF(A14=Output!$A$2:$A$184,IF(C14=Output!$C$2:$C$184,IF(K14=Output!$K$2:$K$184,1))),0))

Use IF-conditionals instead of pairwise multiplications.

You might be getting #N/A (while unexpected), due to stray spaces around the entries. Does it work if modified as follows:

=INDEX(Output!$N$2:$N$184,MATCH(1,IF(A14=TRIM(Output!$A$2:$A$184),IF(C14=TRIM(Output!$C$2:$C$184),IF(K14=TRIM(Output!$K$2:$K$184),1))),0))

confirmed with control+shift+enter?
 
Upvote 0
Aladin,

Thanks for your help. I've tried both versions of the formula below and it's still returning #N/A. Any other suggestions?




Control+shift+enter, not just enter:

=INDEX(Output!$N$2:$N$184,MATCH(1,IF(A14=Output!$A$2:$A$184,IF(C14=Output!$C$2:$C$184,IF(K14=Output!$K$2:$K$184,1))),0))

Use IF-conditionals instead of pairwise multiplications.

You might be getting #N/A (while unexpected), due to stray spaces around the entries. Does it work if modified as follows:

=INDEX(Output!$N$2:$N$184,MATCH(1,IF(A14=TRIM(Output!$A$2:$A$184),IF(C14=TRIM(Output!$C$2:$C$184),IF(K14=TRIM(Output!$K$2:$K$184),1))),0))

confirmed with control+shift+enter?
 
Upvote 0
Aladin,

Thanks for your help. I've tried both versions of the formula below and it's still returning #N/A. Any other suggestions?

That would mean that at least one of the conditions do not hold. What does the following return?

=COUNTIFS(Output!$A$2:$A$184,A14,Output!$C$2:$C$184,C14,Output!$K$2:$K$184,K14)
 
Upvote 0
Getting somewhere now, Aladin.

When I use that formula, it returns 0 but if I use the same formula on the first two conditions (A14 and C14) it returns an answer of 38 which is as I'd expect. When I add the third condition (K14, the question no.), the COUNTIFs formula should find one match.

The third condition (cell K14 which definitely exists in the range K2:K184 in the Output sheet) is 4.01.01 and is right aligned. Is this the problem?


That would mean that at least one of the conditions do not hold. What does the following return?

=COUNTIFS(Output!$A$2:$A$184,A14,Output!$C$2:$C$184,C14,Output!$K$2:$K$184,K14)
 
Upvote 0
Aladin,

Ignore my last reply....an error on my part. The formula which you provided in your first reply works perfectly.

Many thanks.


Getting somewhere now, Aladin.

When I use that formula, it returns 0 but if I use the same formula on the first two conditions (A14 and C14) it returns an answer of 38 which is as I'd expect. When I add the third condition (K14, the question no.), the COUNTIFs formula should find one match.

The third condition (cell K14 which definitely exists in the range K2:K184 in the Output sheet) is 4.01.01 and is right aligned. Is this the problem?
 
Upvote 0
Getting somewhere now, Aladin.

When I use that formula, it returns 0 but if I use the same formula on the first two conditions (A14 and C14) it returns an answer of 38 which is as I'd expect. When I add the third condition (K14, the question no.), the COUNTIFs formula should find one match.

The third condition (cell K14 which definitely exists in the range K2:K184 in the Output sheet) is 4.01.01 and is right aligned. Is this the problem?

What does

=COUNTIFS(K2:K184,"*4.01.01*")

give?
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top