Index + Match/Multi-Vlookup for Items with shared names

marriot

New member
Joined
Sep 29, 2013
Messages
1
Reaction score
0
Points
0
Hi all, I'm having a problem that is giving me a major headache, hoping someone here can help me out.

View attachment Sample.xlsx

In the attached excel file, I'm trying to do a lookup based on two criteria - the class and the number. I used an index/match combo that I think should work, but it didn't register correctly. I keep on getting an error.

I believe this may be due to the fact that there are shared values - some numbers are shared across multiple classes. If that's the case, is there a workaround so that I can properly lookup the item? If not with the index/match, with any other function? (I've tried a vlookup in this case but no luck either)

Thanks!
 

ExcelTactics

New member
Joined
Sep 4, 2013
Messages
9
Reaction score
0
Points
0
Location
Seattle, WA, USA
Website
www.exceltactics.com
Hi Marriot,

To search multiple criteria, you'll need to structure the INDEX(MATCH) as an array formula:

=INDEX(C4:C18,MATCH(1,(A4:A18=A1)*(B4:B18=B1),0))

Enter as an array formula by keying CTRL+SHIFT+ENTER.
 

martindwilson

New member
Joined
Sep 19, 2013
Messages
21
Reaction score
0
Points
0
=INDEX(A4:C18,MATCH(A1&B1,A4:A18&B4:B18,0),3) works just fine but needs array entering
you can use this non array version instead

=INDEX(C4:C18,MATCH(A1&B1,INDEX(A4:A18&B4:B18,0),0))
note you only need the column you are returning from not the whole range but
=INDEX(A4:C18,MATCH(A1&B1,INDEX(A4:A18&B4:B18,0),0),3) would work as well
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
Hi marriot

The LOOKUP function could be another idea.

=LOOKUP(2,1/((A4:A18=A1)*(B4:B18=B1)),C4:C18)
This is a non array formula, just enter to commit.
 
Top