#REF! error when using INDEX MATCH

[alyos]

Hi everyone,

I have a question that I hope is straightforward, but I'm struggling to understand it fully.

In column C, I have a list of part numbers. Each distributor (A, B, C, D) provided us with a quote against different manufacturers. Column A identifies the cheapest price within the range (currently hard coded). Now, I need column B to indicate which distributor the cheapest price applies to: A, B, C, or D.

My formula seems to be working only partially (in cell B5), where the correct value was returned, whereas other cells return #REF!. When I change the first MATCH formula in cell B11 from C11,$C$2:$C$11,0, to C11,$C$7:$C$11,0, it comes up with the correct answer, since I've included data where a duplicate material starts from.

Here is a sample file: Example.xlsx.

Ideally, I want it to look like the screenshot attached (output manually entered).

I hope this explanation makes sense.

Thanks a lot in advance!

 

[Fluff]

How about

=INDEX($E$1:$H$1,,MATCH(A2,E2:H2,0))

 

[alyos]

How about

=INDEX($E$1:$H$1,,MATCH(A2,E2:H2,0))

Thank you - the problem with this approach is that my actual spreadsheet has 5k rows and different materials & prices, so it's quite likely that there will be duplication of prices across different materials. That's the reason why I wanted two criteria to be used, material & cheapest price.

**Edited** Just tried on my actual spreadsheet, and seems to have worked without referencing material part number! I'll sense check the rest of it and let you know

 

[Fluff]

B3 returns #REF because the 1st match returns 2 but you are only indexing a single, so there is no row 2.

 

[alyos]

I'm with you, thank you for explaining.

I really appreciate your help, it turns out the formula is much less comprehensive than I thought!

 

[Fluff]

You're welcome & thanks for the feedback.