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

[marriot]

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]

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]

=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]

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.