INDEX MATCH to return a specific value if a duplicate in detected

[Riptake]

Hi all,

Thanks for reading my post. I had a very rudimentary question but can't seem to get me head around it. I have 2 lists, as seen below:

FYI on a 2 X 6 matrix starting on cell A!

A B
1|Student Name |Age|
2|Student 1 |018|
3|Student 2 |020|
4|Student 3 |023|
5|Student 1 |018|
6|Student 5 |015|


Essentially, trying to use an index match that pull the Age as I input the Student Names. However, as you notice will say Student 1 is ID's twice and it should say "Various".

Don't want to use VBA. Is this possible with an array?


Thanks.

 

[Marcelo Branco]

Assuming you input the Student of interest in D2 maybe (Excel 2007 or higher)

=IF(COUNTIF(A:A,D2)>1,"Various",IFERROR(VLOOKUP(D2,A:B,2,0),"Not Found"))

Hope this helps

M.

 

[Riptake]

Hi Marcelo,

Wanted to know if I could rely on Index-Match instead of VLookUp? Not sure how to alter the formula above.



Thanks in advance.

 

[Riptake]

Marcelo ignore the previous request. I fixed it. However, here is the complication. Your formula is resorting to a default value of "Various" if the Student Name is repeated. However, I need to put in an additional check that says, even if the names are repeated, if they have the same age, the result will show with the actual age and not various. Sorry if my request sounds very convoluted. Thanks!

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Student Name​

[/td][td]

Age​

[/td][td] [/td][td]

Student​

[/td][td]

Age​

[/td][/tr]
[tr][td]

2​

[/td][td]

Student 1​

[/td][td]

18​

[/td][td] [/td][td]

Student 1​

[/td][td]

18​

[/td][/tr]
[tr][td]

3​

[/td][td]

Student 2​

[/td][td]

20​

[/td][td] [/td][td]

Student 2​

[/td][td]

Various​

[/td][/tr]
[tr][td]

4​

[/td][td]

Student 3​

[/td][td]

23​

[/td][td] [/td][td]

Student 3​

[/td][td]

23​

[/td][/tr]
[tr][td]

5​

[/td][td]

Student 1​

[/td][td]

18​

[/td][td] [/td][td]

Student 4​

[/td][td]

Not Found​

[/td][/tr]
[tr][td]

6​

[/td][td]

Student 5​

[/td][td]

15​

[/td][td] [/td][td]

Student 5​

[/td][td]

15​

[/td][/tr]
[tr][td]

7​

[/td][td]

Student 2​

[/td][td]

18​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

8​

[/td][td]

Student 1​

[/td][td]

18​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

9​

[/td][td]

Student 3​

[/td][td]

23​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Array formula in D2 copied down

=IF(COUNTIF(A:A,D2)=0,"Not Found",IF(SUM(IF(FREQUENCY(IF($A$2:$A$9=D2,$B$2:$B$9),$B$2:$B$9),1))>1,"Various",VLOOKUP(D2,A:B,2,0)))

confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Riptake]

Brilliant! Thanks M!!

Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Student Name​

[/TD]
[TD]

Age​

[/TD]
[TD][/TD]
[TD]

Student​

[/TD]
[TD]

Age​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Student 1​

[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD]

Student 1​

[/TD]
[TD]

18​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Student 2​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD]

Student 2​

[/TD]
[TD]

Various​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Student 3​

[/TD]
[TD]

23​

[/TD]
[TD][/TD]
[TD]

Student 3​

[/TD]
[TD]

23​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Student 1​

[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD]

Student 4​

[/TD]
[TD]

Not Found​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

Student 5​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD]

Student 5​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

Student 2​

[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

Student 1​

[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

Student 3​

[/TD]
[TD]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in D2 copied down

=IF(COUNTIF(A:A,D2)=0,"Not Found",IF(SUM(IF(FREQUENCY(IF($A$2:$A$9=D2,$B$2:$B$9),$B$2:$B$9),1))>1,"Various",VLOOKUP(D2,A:B,2,0)))

confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Marcelo Branco]

Brilliant! Thanks M!!

You are welcome and thanks for the feedback.

M.