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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

abuowar

New Member
Joined
Apr 27, 2015
Messages
2
I wish I can help u out :) but I'm also trying to learn how to use that formula :ROFLMAO:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

AndyB63

New Member
Joined
Feb 27, 2015
Messages
23

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

AndyB63

New Member
Joined
Feb 27, 2015
Messages
23

ADVERTISEMENT

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)
 

AndyB63

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Forum statistics

Threads
1,136,444
Messages
5,675,897
Members
419,591
Latest member
mersanko

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
Top